Apply a filter to an automatically joined table

Question

Here's my SQL setup

    create table a
    (
        id serial primary key,
        ta text
    );
    create table b
    (
        id serial primary key,
        tb text,
        aid integer references a(id) not null
    );

Python:

import sqlalchemy as sa
import sqlalchemy.orm

connection_url = "..."
engine = sa.create_engine(connection_url, echo=True, future=True)
mapper_registry = sa.orm.registry()

class A:
    pass


class B:
    pass


mapper_registry.map_imperatively(
    B,
    sa.Table(
        'b',
        mapper_registry.metadata,
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('tb', sa.String(50)),
        sa.Column('aid', sa.ForeignKey('a.id')),
    ))

mapper_registry.map_imperatively(
    A,
    sa.Table(
        'a',
        mapper_registry.metadata,
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('ta', sa.String(50))
    ),
    properties={
        'blist': sa.orm.relationship(B, lazy='joined'),
    },
)

with sa.orm.Session(engine) as session:
    sel = sa.select(A)
    cur = session.execute(sel)
    for rec in cur.unique().all():
        print(rec.A.ta, [b.tb for b in rec.A.blist])

This works fine so far, but now I need to apply a filter to the subtable (B) to include only rows that match the criteria.

 sel = sa.select(A).where(?WHAT?.like('search'))

In other words, how do I write an equivalent of the following SQL in SqlAlchemy?

SELECT *
FROM a
LEFT OUTER JOIN b ON a.id = b.aid
WHERE b.tb like 'search'

How about this one (which I expect to produce empty lists in the target class):

SELECT *
FROM a
LEFT OUTER JOIN b ON a.id = b.aid
    AND b.tb like 'search'

Answer 1

The two solutions (for 2 asked questions) presented below rely on two sqlalchemy functions:

• sqlalchemy.orm.contains_eager to trick sqlalchemy that the desired relationship is already part of the query; and
• sqlalchemy.orm.Query.options to disable the default joinedload configured on the relationship.

---

Question 1

SELECT      *
FROM        a
OUTER JOIN  b 
        ON  a.id = b.aid
WHERE       b.tb like 'search'

Answer 1

is achieved by this query with explanations in line:

sel = (
    sa.select(A)

    # disable 'joinedload' if it remains configured on the mapper; otherwise, the line below can be removed
    .options(sa.orm.lazyload(A.blist))

    # join A.blist explicitely
    .outerjoin(B, A.blist)  # or: .outerjoin(B, A.id == B.aid)

    # add the filter
    .filter(B.tb.like('search'))

    # trick/hint to SQ that the relationship objects are already returned in the query
    .options(sa.orm.contains_eager(A.blist))
)

Question 2

SELECT      *
FROM        a
OUTER JOIN  b 
        ON  a.id = b.aid
        AND b.tb like 'search'

Answer 2

is achieved by this query with explanations in line, but basically the .filter condition is moved into the join condition:

sel = (
    sa.select(A)

    # disable 'joinedload' if it remains configured on the mapper; otherwise, the line below can be removed
    .options(sa.orm.lazyload(A.blist))

    # join A.blist explicitely including the filter
    .outerjoin(B, sa.and_(B.aid == A.id, B.tb.like('search')))

    # trick/hint to SQ that the relationship objects are already returned in the query
    .options(sa.orm.contains_eager(A.blist))
)

---

Warning: you should be careful when using contains_eager and use it in the well defined scope, because you are basically "lying" to the SA model that you have loaded "all" related objects when you might not be. For the purpose of just querying the data it is usually totally fine, but working on modifying and adding to the relationships might lead to some strange results.

Answer 2

Answer 1

You cannot directly filter the joinedload (see The Zen of Joined Eager Loading). You need to add a join to table B and filter the column:

sel = sa.select(A).join(B).filter(B.tb.like('search'))

This results in:

SELECT a.*, b_1.*
FROM a JOIN b ON a.id = b.aid 
LEFT OUTER JOIN b AS b_1 ON a.id = b_1.aid 
WHERE b.tb LIKE 'search'

You see that the usage of Query.join() is to supply JOIN clauses used in subsequent query criterion, and the usage of the joinedload() from the relationship only loads the collection, for each B in the result.

Answer 2

Here you can override the joinedload configured on the mapper with a joinedload using .options including the AND condition.

sel = sa.select(A).options(joinedload(A.blist.and_(B.tb.like('search'))))

This results in the following query:

SELECT * 
FROM a 
LEFT OUTER JOIN b ON a.id = b.aid 
    AND b.tb LIKE 'search'