CODEWITHSUNDEEP
pythonsqlalchemy
Matthew Moisen
Matthew Moisen

Reputation: 18329

How to combine many "x AND y" conditions with OR for WHERE clause

I have a many to many junction table similar to the following:

        FooBar
-----------------------
id    | foo_id | bar_id
1     |    1   |    1
2     |    1   |    2
3     |    2   |    3
4     |    3   |    4

and a list of (foo_id, bar_id), such as:

items = [(1, 1), (1, 2), (2, 3), (3, 4)

How can I perform the following query in SQLAlachemy?

SELECT id
FROM foo_bar
WHERE (foo_id = 1 AND bar_id = 1)
   OR (foo_id = 1 AND bar_id = 2)
   OR (foo_id = 2 AND bar_id = 3)
   OR (foo_id = 3 AND bat_id = 4)

I have attempted variations of the following, but all of them return a WHERE clause containing only ANDs

from sqlalchemy import and_, or_
q = session.query(FooBar)
for item in items:
    q = q.filter(or_(and_(FooBar.foo_id == item[0], FooBar.bar_id == item[1])))

print q.statement
for item in

Upvotes: 2

Views: 379

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 53007

The problem is that a call to or_() with a single expression is basically just the expression, as there's nothing to OR with. Repeated calls to Query.filter() append new predicates combined with AND. Move the iteration over items inside the filter(or_()):

q = q.filter(or_(*(and_(FooBar.foo_id == foo_id, FooBar.bar_id == bar_id)
                   for foo_id, bar_id in items)))

This generates a generator expression, which is then unpacked as positional arguments to or_().

If using a backend that supports composite IN construct, such as Postgresql and MySQL, you could replace this with tuple_().in_:

from sqlalchemy import tuple_

...

q = q.filter(tuple_(FooBar.foo_id, FooBar.bar_id).in_(items))

Upvotes: 4

Related Questions