CODEWITHSUNDEEP
pythonsqlalchemy
vezzon
vezzon

Reputation: 209

sqlalchemy dynamic filtering with and_ and or_

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?

Upvotes: 0

Views: 1170

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

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

Upvotes: 1

Related Questions