Support for Placing Aggregate Filters in HAVING Clause

[UmakanthKaspa]

Description:

When a filter with an aggregate function is added (e.g., (SUM(\tabGL Entry.debit` - `tabGL Entry.credit`) > 0)), the generated SQL places the filter in the WHERE` clause by default:

SELECT count(*) AS `Party`
FROM `tabGL Entry` AS `tabGL Entry`
WHERE SUM(`tabGL Entry`.debit - `tabGL Entry`.credit) > 0
LIMIT 100;

This query results in an SQL error because aggregate functions like SUM cannot be used in the WHERE clause. Instead, they should be placed in the HAVING clause to ensure proper execution after aggregation.

Expected SQL:

SELECT count(*) AS `Party`
FROM `tabGL Entry` AS `tabGL Entry`
GROUP BY `tabGL Entry`.party
HAVING SUM(`tabGL Entry`.debit - `tabGL Entry`.credit) > 0
LIMIT 100;

---

Proposed Solution:

1. Enhance Query Logic:

   • Automatically detect aggregate functions in filters and place them in the HAVING clause.

2. Provide User Control:

   • Add an option in the filter configuration UI for users to explicitly specify whether a filter should apply to the WHERE or HAVING clause.

---

Steps to Reproduce:

1. Open Frappe Insights.
2. Create a visual type "Query".
3. Select the data source.
4. Select the table Gl Entry.
5. Select the column Party.
6. Add a filter using the expression (SUM(\tabGL Entry.debit` - `tabGL Entry.credit`) > 0)`.
7. Generate the SQL query.
8. Observe that the filter is incorrectly placed in the WHERE clause, causing an error.

Screenshots: