CODEWITHSUNDEEP
pythondjangopostgresql
Jefferson Houp
Jefferson Houp

Reputation: 856

Dynamically making queries

I am trying to find best way for dynamically making queries. Front-end of the project will send json to back-end for getting query of objects by parameters with query:

Model.objects.filter(**query)

I can set json format on my wish. In this moment json:

# first case
{'type': 'channel_type', 'or': {'param1': {'__gte': 10}, 'param2': {'': 12}}}
# second case
{'type': {'in': ['channel1', 'channel2']}, 'and': {'p1': {'__lte': 12}, 'p2': {'!=': 1}}}

And that will be equal in sql (select x from y - will be static, code only generate 'where' condition):

# first case
WHERE table.type = channel_type AND (table.param1 >= 10 OR table.param2 = 12)
# second case
WHERE table.type IN [channel1, channel2] AND (table.p1 <= 12 AND table.p2 <> 1)

I already did that:

query_params_dict = {'>': '__gt', '<': '__lt', '>=': '__gte', '<=': '__lte', '==': ''}

def parse_segmentation_query(query):
    type = {'type': query.pop('type')} if isinstance(query['type'], str) else {'type__in': query.pop('type')['in']}
    query = recursive_parsing_query(query)
    query.update(type)
    return query

Func "recursive_parsing_query" not ready, so not shown. But I want recursively pop params from dict and make {'param1__gte': 2} dict which will passed in **kwards for objects.filter. Trouble is getting "non-equal" condition => It breaks simple-writed "Model.objects.filter(**query), because there we need "exclude".

I'm wondering: is this way best for dynamically making queries? Or you can offer better? If not, so how can I pass excludes with only-filter condition ?

upd: for sure json-query will be deeper

UPDATE:

Finally, I did that (dynamic-querying by filtering with numeric fields), thanks for MJafar Mash:

condition_types = {'>': '__gt', '<': '__lt', '>=': '__gte', '<=': '__lte', '==': '', '!=': ''}
q_conditions = {'or': True, 'and': False}


def recursive_parsing_query(query, or_condition=False):
    filter_obj = Q()
    for key, value in query.items():
        if key in q_conditions:
            local_filter = recursive_parsing_query(value, q_conditions[key])
        else:
            local_filter = simple_parsing_query(key, value)
        if or_condition:
            filter_obj |= local_filter
        else:
            filter_obj &= local_filter
    return filter_obj


def simple_parsing_query(parameter_name, parameter_query):
    local_filter = Q()
    for condition_type, condition_value in parameter_query.items():
        parameter_condition = {parameter_name + condition_types[condition_type]: condition_value}
        if condition_type != '!=':
            local_filter = Q(**parameter_condition)
        else:
            local_filter = ~Q(**parameter_condition)
    return local_filter

Upvotes: 1

Views: 119

Answers (1)

Mohammad Jafar Mashhadi
Mohammad Jafar Mashhadi

Reputation: 4251

Inspired by the way filter and exclude are internally implemented, I suggest rewriting your query generation algorithm to use Q objects then I suppose you won't have any problems with the negation or the recursive-ness of the query generation algorithm.

Upvotes: 2

Related Questions