CODEWITHSUNDEEP
sqlalchemy
Cyril N.
Cyril N.

Reputation: 39869

Filter a SQLAlchemy object using a direct SQL query?

I'm using the declarative base and filter my searches like this :

MyModel.query.filter(MyModel.id == 5)

But I need to obtain the 5 using a complex query involving many joins.

Is there a way to do the following in SQLAlchemy:

MyModel.query.filter(MyModel.id == text('SELECT a.id FROM accounts a LEFT JOIN sessions s ON s.account_id = a.id LEFT JOIN suitable t ON t.session_id = s.id'))

BUT, There is a twist:

I know I can just do the first query, get the resulting id, and then call the MyModel.query.filter(MyModel.id == result).

What I'm looking at is a way to tell SQLAlchemy to generate a query such as:

SELECT ... from MyModel WHERE MyModel.id = (SELECT a.id FROM accounts a LEFT JOIN sessions s ON s.account_id = a.id LEFT JOIN suitable t ON t.session_id = s.id)

In order to only have only one query executed, instead of two.

Upvotes: 0

Views: 29

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

You can do it using .in_():

q = select(Parent).where(
    Parent.id.in_(text("SELECT id FROM parent WHERE last_name = 'Simpson'"))
)
print(q)
"""
SELECT parent.id, parent.last_name 
FROM parent 
WHERE parent.id IN (SELECT id FROM parent WHERE last_name = 'Simpson')
"""
with Session(engine) as sess:
    result = sess.scalars(q).first()
    print(result)  # <__main__.Parent object at 0x7f1681d559a0>
    print(result.last_name)  # Simpson

Upvotes: 1

Related Questions