How to construct a sqlalchemy filter with ((condition1 AND condition2) OR (condition2 AND condition3))?

Question

query.filter(a == 'a', b =='b').filter(a == 'a2', b == 'b2')

Will create a filter that is essentially (condition1 AND condition2) AND (condition3 AND condition4).

Instead, I want it to be (condition1 AND condition2) OR (condition3 AND condition4). I also want to potentially chain a variable amount of these outside ORs.

Is this possible in SQLAlchemy?

Does this answer your question? [Using OR in SQLAlchemy](https://stackoverflow.com/questions/7942547/using-or-in-sqlalchemy) — Ruben Helsloot, Oct 28 '20 at 14:36
@Ruben I've seen those. None of those questions have "AND" in the middle of their conditions? They simply show condition1 OR condition2. Unless I'm missing something. — ct21, Oct 28 '20 at 22:51

score 0 · Accepted Answer · answered Oct 28 '20 at 22:58

It helps to look at your conditions like building blocks. First, you put the smallest ones together, which are the two ANDs:

and_(a == 'a', b == 'b'),
and_(a == 'a2', b == 'b2')

And then you can connect them with an OR:

query.filter(
  or_(
    and_(a == 'a', b == 'b'),
    and_(a == 'a2', b == 'b2')
  )
)

Alternatively, if you like binary operators, the following is equivalent:

query.filter(
  (a == 'a' & b == 'b')
  | (a == 'a2' & b == 'b2')
)

How to construct a sqlalchemy filter with ((condition1 AND condition2) OR (condition2 AND condition3))?

1 Answers1