基于kendo UI过滤器动态构建Django Query

Question

我正在尝试基于用户过滤器查询数据库。我收到了来自剑道UI网格的以下输入。

{
"filter":{
"filters":[
{
"logic":"or",
"filters":[
{
"field":"aging",
"operator":"eq",
"value":24
},
{
"field":"aging",
"operator":"eq",
"value":13
}
]
},
{
"logic":"or",
"filters":[
{
"field":"follow_up_name",
"operator":"eq",
"value":"Call Insurance Provider"
}
]
},
{
"logic":"or",
"filters":[
{
"field":"patient_name",
"operator":"eq",
"value":"kartik"
}
]
},
{
"logic":"and",
"filters":[
{
"field":"created_date",
"operator":"eq",
"value":"2022-01-09T18:30:00.000Z"
},
{
"field":"created_date",
"operator":"gte",
"value":"2022-01-04T18:30:00.000Z"
}
]
},
{
"logic":"or",
"filters":[
{
"field":"follow_up_status",
"operator":"eq",
"value":"Open"
}
]
},
{
"logic":"or",
"filters":[
{
"field":"role_name",
"operator":"eq",
"value":"Pharmacist"
}
]
},
{
"logic":"or",
"filters":[
{
"field":"last_response",
"operator":"eq",
"value":"To-Patient"
}
]
}
],
"logic":"and"
},
"skip":0,
"take":10
}

基于以上数据，我需要"one_answers"&amp或'条件以动态生成查询。并将其传递给数据库。过滤器也可以包含多个列表。还想让这些类变得通用，它们只能接受UI参数来构建查询和返回。请提供解决方案。

Answer 1

Q对象是您的朋友。

from django.db.models import QuerySet, Q
from functools import reduce
def filter_rec(data: dict) -> Q:
logic = data.get('logic', None)
# Leaf node
if logic is None:
field = data['field']
operator = data['operator']
value = data['value']
return Q(**{f'{field}__{operator}': value})
# Node with children
children = list()
for item in data['filters']:
child = filter_rec(child)
children.append(child)
if logic == 'and':
query = reduce(lambda x, y: x & y, children)
elif logic == 'or':
query = reduce(lambda x, y: x | y, children)
return query
def kendo_filter(data: dict, qs: QuerySet) -> QuerySet:
query = filter_rec(data['filter'])
skip = data['skip']
take = data['take']
filtered_qs = qs.filter(query)
paginated_qs = filtered_qs[skip:skip+take]
return paginated_qs

大意是这样的。由您来验证数据并进行错误处理。

filter_rec(data['filter'])为您的示例数据返回以下内容：

<Q: (
AND: 
(OR: ('aging__eq', 24), ('aging__eq', 13)), 
('follow_up_name__eq', 'Call Insurance Provider'), 
('patient_name__eq', 'kartik'), 
('created_date__eq', '2022-01-09T18:30:00.000Z'), 
('created_date__gte', '2022-01-04T18:30:00.000Z'), 
('follow_up_status__eq', 'Open'), 
('role_name__eq', 'Pharmacist'), 
('last_response__eq', 'To-Patient')
)>

由于单个OR将按原样提升，嵌套的and将显示为单个级别，因此它似乎起到了作用。