Filter a SQLAlchemy object using a direct SQL query?

Question

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.

Gord Thompson · Accepted Answer · 2023-01-24 12:20:46Z

1

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

answered Jan 24, 2023 at 12:20

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cyril N. Over a year ago

That's super interesting, thank you! If I can ensure that the subquery will always return only one row, is there a way to replace the IN by equal somehow?

Cyril N. Over a year ago

Also, if my subquery needs variables ("like WHERE account_id = :id"), how can I do that ?

Collectives™ on Stack Overflow

Filter a SQLAlchemy object using a direct SQL query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related