sqlalchemy dynamic filtering with and_ and or_

Question

Based on this approach, i want built a dynamic filter with multiple and_ and or_ levels. My filter conditions looks like this:

filters = [{Contact.shown_name: 'a', Contact.town: 'b'}, {Contact.shown_name: 'c'}]

The conditions in one dictionary should be in a AND-clause. The dictionarys itself should be in an OR-clause.

I want the result from this filter:

    .filter(or_(and_(Contact.shown_name.ilike('%a%'), Contact.town.ilike('%b%')),
                and_(Contact.shown_name.ilike('%c%'))))

I want to filter all items where the shown_name contains 'a' and the town contains 'b' OR all items where the shown_name contains 'c'.

The number of condition and dictionarys in the list 'filters' is flexible.

How can I do it?

Ilja Everilä · Accepted Answer · 2020-10-08T13:14:48.350

1

You can write the logic using nested comprehensions and argument unpacking.

filters = or_(*[and_(*[c.ilike(f"%{v}%") for c, v in f.items()]) for f in filters])

If you prefer a more verbose approach:

# This assumes there's always at least one filter in the list
res = literal(False)
for f in filters:
    tmp = literal(True)
    for c, v in f.items():
        tmp &= c.ilike(f"%{v}%")

    res |= tmp

filters = res

edited Oct 08 '20 at 13:14

answered Oct 08 '20 at 07:58

Ilja Everilä

50,538
7
126
127

is this python 3.9 syntax? ( the |= and &=) ? – c8999c 3f964f64 Oct 08 '20 at 08:12
Is it possible, you have forget '.items()' on iter through the dict. This code works as expected: filters = or_(*[and_(*[c.ilike(f"%{v}%") for c, v in f.items()]) for f in filters]) – vezzon Oct 08 '20 at 09:03
Yeah, forgot `items()`. – Ilja Everilä Oct 08 '20 at 13:08
@c8999c3f964f64 Augmented assignment operations have been a part of Python for quite some time, at least since v2.0, if not before. – Ilja Everilä Oct 08 '20 at 13:10
1

oh yeah, nevermind, the change in 3.9 was specifically about the usage with the built-in dict class – c8999c 3f964f64 Oct 08 '20 at 13:17

sqlalchemy dynamic filtering with and_ and or_

1 Answers1