Re: [sqlalchemy] Nested Exists Query produces join

[Mike Bayer]

On Fri, Feb 9, 2018 at 5:46 AM,   wrote:
> I'm trying to do a Many-to-Many query for all rows in `AModel` that don't
> have a relationship with every `BModel` row.
>
> # The schema that looks like this:
> class AModel(Base):
> __tablename__ = 'amodels'
> id = Column(Integer, primary_key=True)
> relationship('BModel', secondary='abjoinmodels',
> back_populates='relateds')
>
> class BModel(Base):
> __tablename__ = 'bmodels'
> id = Column(Integer, primary_key=True)
> relationship('AModel', secondary='abjoinmodels',
> back_populates='relateds')
>
> class ABJoinModel(Base):
> __tablename__ = 'abjoinmodels'
> a_id = Column(Integer, ForeignKey('amodels.id'), primary_key=True)
> b_id = Column(Integer, ForeignKey('bmodels.id'), primary_key=True)
>
>
> # And here's some mock data:
> b_instances = [BModel(), BModel()]
> a_instances = [
> AModel(relateds=b_instances),
> AModel(relateds=b_instances[0:1]),
> AModel(),
> ]
> dbsession.add_all(a_instances)
> dbsession.flush()
>
>
> # Now this query extracts all the rows appropriately:
> # -- at least one relationship missing
> # SELECT a.id
> # FROM a
> # WHERE EXISTS (
> #   SELECT 1
> #   FROM b
> #   WHERE NOT EXISTS (
> # SELECT 1
> # FROM ab_join
> # WHERE a.id = ab_join.a_id AND b.id = ab_join.b_id
> #   )
> # );
> # -- produces 2, 3
>
> # However, trying to map this to SQLAlchemy, I get this:
> query = Query(AModel).filter(
> Query(BModel).filter(
> Query(AModel.relateds.prop.secondary).filter(
> and_(
> BModel.relateds.prop.secondary.c.a_id == AModel.id,
> BModel.relateds.prop.secondary.c.b_id == BModel.id,
> )
> ).exists()
> ).exists()
> )

for this you need to use correlate() to give it a hint where it is correlating:

q = s.query(AModel.id).filter(
s.query(BModel).filter(
~s.query(ABJoinModel).filter(
and_(AModel.id == ABJoinModel.a_id, BModel.id)
).correlate(AModel, BModel).exists()
).exists()
)


produces:

SELECT amodels.id AS amodels_id
FROM amodels
WHERE EXISTS (SELECT 1
FROM bmodels
WHERE NOT (EXISTS (SELECT 1
FROM abjoinmodels
WHERE amodels.id = abjoinmodels.a_id AND bmodels.id)))


looks right




>
> # which produces the following statement:
> # SELECT amodels.id AS amodels_id
> # FROM amodels
> # WHERE EXISTS (SELECT 1
> # FROM bmodels
> # WHERE EXISTS (SELECT 1
> # FROM abjoinmodels, amodels
> # WHERE abjoinmodels.a_id = amodels.id AND abjoinmodels.b_id = bmodels.id))
>
> Query.with_session(dbsession).all()
> # []
>
> And, therefore doesn't return ANY rows.
>
> How should I be constructing this? I'm trying to generalize this, so I'd
> also like to be able to do this same query on a One-to-Many mapping too
> (where B.a_id = Column(Integer, ForeignKey('amodels.id'))).
>
> Thanks!
> Devin
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Nested Exists Query produces join

[devin]

I'm trying to do a Many-to-Many query for all rows in `AModel` that don't 
have a relationship with every `BModel` row.

# The schema that looks like this:
class AModel(Base):
__tablename__ = 'amodels'
id = Column(Integer, primary_key=True)
relationship('BModel', secondary='abjoinmodels', 
back_populates='relateds')

class BModel(Base):
__tablename__ = 'bmodels'
id = Column(Integer, primary_key=True)
relationship('AModel', secondary='abjoinmodels', 
back_populates='relateds')

class ABJoinModel(Base):
__tablename__ = 'abjoinmodels'
a_id = Column(Integer, ForeignKey('amodels.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('bmodels.id'), primary_key=True)


# And here's some mock data:
b_instances = [BModel(), BModel()]
a_instances = [
AModel(relateds=b_instances),
AModel(relateds=b_instances[0:1]),
AModel(),
]
dbsession.add_all(a_instances)
dbsession.flush()


# Now this query extracts all the rows appropriately:
# -- at least one relationship missing
# SELECT a.id
# FROM a
# WHERE EXISTS (
#   SELECT 1
#   FROM b 
#   WHERE NOT EXISTS (
# SELECT 1
# FROM ab_join
# WHERE a.id = ab_join.a_id AND b.id = ab_join.b_id
#   )
# );
# -- produces 2, 3

# However, trying to map this to SQLAlchemy, I get this:
query = Query(AModel).filter(
Query(BModel).filter(
Query(AModel.relateds.prop.secondary).filter(
and_(
BModel.relateds.prop.secondary.c.a_id == AModel.id,
BModel.relateds.prop.secondary.c.b_id == BModel.id,
)
).exists()
).exists()
)

# which produces the following statement:
# SELECT amodels.id AS amodels_id
# FROM amodels
# WHERE EXISTS (SELECT 1
# FROM bmodels
# WHERE EXISTS (SELECT 1
# FROM abjoinmodels, amodels
# WHERE abjoinmodels.a_id = amodels.id AND abjoinmodels.b_id = bmodels.id))

Query.with_session(dbsession).all() 
# []

And, therefore doesn't return ANY rows.

How should I be constructing this? I'm trying to generalize this, so I'd 
also like to be able to do this same query on a One-to-Many mapping too 
(where B.a_id = Column(Integer, ForeignKey('amodels.id'))).

Thanks!
Devin

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.