[Tutorial] SQL query with `OR` clause. Custom Script

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.

1 Like

Or filter in get_list

frappe.get_list("ToDo",
		fields=["name", "description"] if as_list else "count(*)",
		filters=[["ToDo", "status", "=", "Open"]],
		or_filters=[["ToDo", "owner", "=", frappe.session.user],
			["ToDo", "assigned_by", "=", frappe.session.user]],
		as_list=True)
1 Like

Another way is using in operator, since it have the same effect for the first example

condition = {
   "item_code": ["in", ['JEN 222725', 'JEN 567754']],
   "brand": "Jenbancher",
   "item_group": "Engine"
}
2 Likes

I’ve searched in forum and documentation and it is not mentioned anywhere. Thanks.