Contains_eager And Limits In Sqlalchemy

I have 2 classes: class A(Base): id = Column(Integer, primary_key=True) name = Column(String) children = relationship('B') class B(Base): id = Column(Integer, prima

Solution 1:

First, take a step back and look at the SQL. Your current query is

SELECT * FROM a JOIN b ON b.id_a = a.id WHERE b.name == '...' LIMIT 50;

Notice the limit is on a JOIN b and not a, but if you put the limit on a you can't filter by the field in b. There are two solutions to this problem. The first is to use a scalar subquery to filter on b.name, like this:

SELECT*FROM a
WHEREEXISTS (SELECT1FROM b WHERE b.id_a = a.id AND b.name ='...')
LIMIT 50;

This can be inefficient depending on the DB backend. The second solution is to do a DISTINCT on a after the join, like this:

SELECTDISTINCT a.* FROM a JOIN b ON b.id_a = a.id
WHERE b.name == '...'
LIMIT 50;

Notice how in either case you do not get any column from b. How do we get them? Do another join!

SELECT * FROM (
    SELECTDISTINCT a.* FROM a JOIN b ON b.id_a = a.id
    WHERE b.name == '...'
    LIMIT 50;
) a JOIN b ON b.id_a = a.id
WHERE b.name == '...';

Now, to write all of this in SQLAlchemy:

subquery = (
    session.query(A)
           .join(B)
           .with_entities(A)  # only select A's columns
           .filter(B.name == '...')
           .distinct()
           .limit(50)
           .subquery()  # convert to subquery
)
aliased_A = aliased(A, subquery)
query = (
    session.query(aliased_A)
           .join(B)
           .options(contains_eager(aliased_A.children))
           .filter(B.name == "...")
)