CODEWITHSUNDEEP
pythonsqlalchemy
Ray
Ray

Reputation: 4947

How to OR multiple filter calls in python

I have the following code in Python that takes in several filters. Then I have a loop that calls .filter on each of them. However when you call multiple filters these are ANDed. How can I change it so that the multiple calls to filter are ORed. The problem is in the "else" part of my code.

for filter_ in filters:
        field_models = {'type': ProductType, 'category': ProductCategory}
        if filter_['field'] in field_models:
            model = field_models[filter_['field']]
            organizations_products = organizations_products.join(model).filter(or_(
                model.code.ilike('%{}%'.format(escape_like(filter_['value']))),
                model.description.ilike('%{}%'.format(escape_like(filter_['value'])))
            ))
        else:
            field = getattr(Product, filter_['field'])
            organizations_products = organizations_products.filter(
                field.ilike('%{}%'.format(escape_like(filter_['value']))))

Upvotes: 1

Views: 137

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28313

There are two parts to the solution. First, we must construct the from clause, and then the where clause.

def get_joined_stmt(filters, stmt):
    if 'type' in filters.keys():
        stmt = stmt.join(ProductType)
    if 'category' in filters.keys():
        stmt = stmt.join(ProductCategory)
    return stmt

def get_exprs(field, value):
    def _ilike_expr(x): return '%{}%'.format(escape_like(x))

    model_dict = {'type': ProductType, 'category': ProductCategory}
    model = model_dict[field]
    stmt = organizations_products.join(model)
    try: 
        return [model.code.ilike(_ilike_expr(value)),
                model.description.ilike(_ilike_expr(value))]
    except KeyError:
        return [getattr(Product, field).ilike(_ilike_expr(value))]

organizations_products = get_joined_stmt(filters, organizations_products)

where_exprs = []
for filter_ in filters.items():
    where_exprs.extend(get_exprs(**filter_))

organizations_products = organizations_products.filter(or_(*where_exprs))

Upvotes: 1

univerio
univerio

Reputation: 20548

Just build up a list of filters and or_ them at the end:

exprs = []
for filter_ in filters:
    exprs.append(field.ilike(...))
organizations_products = organizations_products.filter(or_(*exprs))

By the way, implementing search like this is a terrible way to go performance-wise (unless you're on PostgreSQL and have a trigram index, in which case ignore this). You're much better served by using the fulltext search features of your DB.

Upvotes: 0

Related Questions