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.

Reply via email to