CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Andi Domi
Andi Domi

Reputation: 751

How to create multiple-sub "where" "in" queries in flask_sqlalchemy or SQLAlchemy?

I am new to SQLAlchemy so bear a bit with me if the question is not properly articulated.

But I have a particular problem I would like to solve. Let say we have a parent, child, and toy table. Where we have a m2m relationship between family-parent, parent-child and child-toy.

Now if we would like to gather all family in which a toy is present ( a child can be part of the parent family and grandparents family) we would do:

select * from family where parent_id in ( select parent_id from parent where child_id in (select child_id from child where toy_id in (1,2)))

And I would like to convert this to a SQLAlchemy or flask_sqlalchemy query. One way I know of is to do something like :

Toy.query.filter(Toy.id.in_((1, 2))).all()

Or in pure SQLAlchemy:

session.query(Toy).filter(Toy.id.in_((1, 2))).all()

Then get the output put it into a list comprehension and use the list as the input of another where in.

But this feels very inefficient as the query would be evaluated on each filter/list comprehension creation.

Is there any way to have this query in a single flask_sqlalchemy or just pure SQLAlchemycommand, in such a way that it can be evaluated only once?

Thank you in advance for your help!

Upvotes: 2

Views: 326

Answers (2)

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

You can actually create a filter_list, and then execute the query at once as you described

filter_list = list()
filter_list.append(Child.toy_id.in_([1,2]))
filter_list.append(Child.toy_id.in_([4,5]))  # some other condition...
# more conditions here...
result = session.query(Child).filter(*filter_list).all()

the *filter_list syntax automatically applies all the conditions you appended to the filter_list

Upvotes: 3

Willian Vieira
Willian Vieira

Reputation: 746

try use subquery:

ch = session.query(Child.child_id).filter(Child.toy_id.in_([1,2])).subquery('ch')
p = session.query(Parent.parent_id).filter(Parent.child_id.in_(ch)).subquery('p')
result = session.query(Family).filter(Family.parent_id.in_(p)).all()

Upvotes: 3

Related Questions