dynamically generate filters in sqlalchemy with mix of and/or and not/like

Question

I have a dictionary that defines some configuration, where I will not know in advance the exact filters that my user wants to build

it's specified like this:

{"col1": ["val1", "~val2"],  
 "col2": ["val3", "val4"], 
 "col3": ["val5", "val6", "~val7", "~val8"]}

where this should translate into:

WHERE ( 
  col1 LIKE val1 
  AND 
  col1 NOT LIKE val2 
) 
OR ( 
  col2 LIKE val3 
    OR 
  col2 LIKE val4 
) 
OR 
( 
  ( 
    col3 LIKE val5 
    OR 
    col3 LIKE val6 
  ) 
  AND 
    col3 NOT LIKE val7 
  AND 
    col3 NOT LIKE val8 
)

the criteria at the top level is always an 'or' (you either match one column as specified or match another column as specified) never 'and'

however, within the list, you can match any of the positive criteria but none of the negative criteria (criteria here being strings)

my attempt so far at building this dynamically looks something like this:

def make_filter(definition):
    f = {}
    for variable in definition:
        v = getattr(Target, variable)
        f[v] = []
        for col in definition[variable]:
            f[v].append(col)
    return f


def make_query(def_dict):
    q = destination.query(Target)
    filter = make_filter(def_dict)
    for col in filter:
        for val in filter[col]:
            if '~' in val:
                q = q.filter(sa.not_(col.like(val)))
            else:
                q = q.filter(col.like(val))
    for record in q.all():
        # do stuff

but obviously, that combines all the criteria by 'and' - I can't see a way to dynamically combine with 'or' for an unknown number of conditions...

I can modify the specifications if that's a better way to solve this problem as long as the same set of criteria can be specified unambiguously. I haven't specifically required users to put all positive criteria before all negative criteria, but that's reasonable for instance.

when you say `"within the list, you can match any of the positive values but none of the negative values"`, is `val2` here: `["val1", "~val2"]` a "negative" value? Or might `val2` be `-3`, for example and so the value in the list would be `"~-3"` and that should be ignored altogether? — SuperShoot, Nov 01 '18 at 04:56
See here: https://stackoverflow.com/questions/3618051/sqlalchemy-produce-or-clause-with-multiple-filter-calls, https://stackoverflow.com/questions/2678600/how-do-i-construct-a-slightly-more-complex-filter-using-or-or-and-in-sqlalchem, https://stackoverflow.com/questions/28924525/dynamics-or-filtering-with-sqlalchemy — Ilja Everilä, Nov 01 '18 at 04:57
https://stackoverflow.com/questions/34838302/sqlalchemy-adding-or-condition-with-different-filter — Ilja Everilä, Nov 01 '18 at 05:05
ha I should have said negative cases instead of negative values because I meant something like "where result not like 'string'" - I'll change it @SuperShoot — gkennos, Nov 01 '18 at 05:06
ah thankyou @IljaEverilä I did search but didn't surface those - thankyou I like the first one that will suit my needs nicely — gkennos, Nov 01 '18 at 05:11
At a risk of being overly chatty/meta, you're not alone: it has been increasingly difficult to find relevant questions and answers from SO using google. — Ilja Everilä, Nov 01 '18 at 05:14

score 0 · Accepted Answer · answered Nov 01 '18 at 21:37

Not sure if this should be marked as a dupe given the linked answers provided above - possibly different enough to still be helpful so just in case I'm posting my solution based on the linked answers for completeness:

def make_filter(definition):
    f = {}
    for variable in definition:
        v = getattr(Target, variable)
        f[v] = {"like": [], "not": []}
        for col in definition[variable]:
            if "~" in col:
                f[v]["not"].append(re.sub('~', '', col))
            else:
                f[v]["like"].append(col)
    return f

def make_query(def_dict):
    q = destination.query(Target)
    filter = make_filter(def_dict)
    for item in filter:
        like_this = filter[item]["like"]
        not_this = filter[item]["not"]
        if len(not_this):
            for nt in not_this:
                q = q.filter(sa.not_(item.like(nt)))
        clauses = []
        for lt in like_this:
            clauses.append(item.like(lt))
        q = q.filter(sa.or_(*clauses))
        for record in q.all():
            # do stuff

dynamically generate filters in sqlalchemy with mix of and/or and not/like

1 Answers1