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