On Fri, Feb 9, 2018 at 5:46 AM,  <de...@devinfee.com> 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.

Reply via email to