Hi all,
I’ve seen that in frappe library if you want to filter something with OR you have to do your custom script. I am sure that there have been a lot of people who’ve had this issue.
I am going to share my code, and how I use it:
def custom_script(condition):
"""
This function generates a custom SQL Query where return custom data based on condition and some rules.
For ex.
condition = [{
'item_code': 'JEN 222725',
}, {
'item_code': 'JEN 567754',
}, {
'brand': 'Jenbancher',
'item_group': 'Engine'
}]
returns `item_code = 'JEN 222725' or item_code = 'JEN 567754' or brand = 'Jenbancher' and item_group = 'Engine'`.
The keys in one dictionary generates `AND` clause, whereas the combination of dictionaries generate `OR` clause.
:param condition: List of dictionaries.
:return: List of dictionaries.
"""
condition = [{
'item_code': 'JEN 222725',
}, {
'item_code': 'JEN 567754',
}, {
'brand': 'Jenbancher',
'item_group': 'Engine'
}]
doctype = {
'name': 'Item',
'fields': ['description', 'item_code', 'item_group', 'item_name', 'last_purchase_rate', 'manufacturer_pn',
'name', 'stock_uom']
}
# Generate columns
columns = ", ".join(doctype['fields'])
"""
Equivalent to this code:
where_clause = []
for x in item_code:
and_operator = ' and '.join(["%s = '%s'" % (key, value) for (key, value) in x.items()])
where_clause.append(and_operator)
print(where_clause)
where_clause = ' or '.join(where_clause)
"""
where_clause = ' or '.join(
"" + ' and '.join(["%s = '%s'" % (key, value) for (key, value) in k.items()]) + "" for k in condition)
# Generate select query
q = """select {columns} from `tab{table_name}` where {clause}""".format(columns=columns,
clause=where_clause,
table_name=doctype['name'])
sql = frappe.db.sql(q, as_dict=True)
return sql
I really hope it helps.
It is a small topic, but a quick solution.
NOTE: If you have better approaches feel free to reply.