Wednesday, 4 May 2022

Apply a filter to an automatically joined table

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'


from Apply a filter to an automatically joined table

No comments:

Post a Comment