Danny asked 10 years ago
Hello! First at all, sorry by my poor english.
I want to show a list of customers and the last column of the grid is a "calculated field" in my sql. For example:
$g->select_command = "SELECT clients.id, clients.name, SUM(invoices.id) AS num_invoices FROM clientes LEFT JOIN invoices ON clients.id=invoices.clientid GROUP BY clients.id";
No problem. It works fine.
The problem comes now. I would like to filter the data usign that calculated field, but is not posible because the SQL would use a HAVING condition and not a WHERE condition.
PHPgrid returns "Invalid use of group function" error because "SUM(invoices.id)=myvalue" would be placed in a HAVING condition, but PHPgrid place it in the WHERE condition.
PHY grid tries "SELECT clients.id, clients.name, SUM(invoices.id) AS num_invoices FROM clientes LEFT JOIN invoices ON clients.id=invoices.clientid WHERE SUM(invoices.id)=5 GROUP BY clients.id", and this fails.
It should be "SELECT clients.id, clients.name, SUM(invoices.id) AS num_invoices FROM clientes LEFT JOIN invoices ON clients.id=invoices.clientid GROUP BY clients.id HAVING SUM(invoices.id)=5"
How can I do for adding "SUM(invoices.id)=5" in HAVING condition and not in WHERE condition
Abu Ghufran answered 10 years ago
This is not currently supported. I'll update you after some working.
Abu Ghufran answered 10 years ago
Here is code for an alternate solution.
http://pastebin.com/fvdwGv80
It uses custom onselect event and overrides the sql with HAVING clause when grouping column is searching.
Ed KAbu Ghufran answered 8 years ago
Hi Ek,
Above method is not preferred now.
Library now support having clause search by column settings.
You can email me your order number for update.
Johan answered 6 years ago
The link you are referring to is not working. What is the column setting for using a “having clause” instead of a “where clause”?
Abu Ghufran Staff answered 6 years ago
I’ve fixed the link: The main reply is:
All you need is to set, e.g.:
$col[“dbname”] = “COUNT(studies.PMID)”;
It will detect aggregate functions and use having instead of where clause.
Johan answered 6 years ago
I don’t get it. My sql query is like this:
$grid->select_command = “SELECT o_und.ID as id, o_und.company_id as company_id, o_huv.typ_1 as huvudregel from omsUndantagsregler o_und LEFT JOIN omsHuvudundantagsregel o_huv on o_und.huvuduntantagsregel_id = o_huv.id”;
I’d like to be able to perform a search on “huvudregel”. What should ‘$col[“dbname”]’ be?
Abu Ghufran Staff answered 6 years ago
When you define column for huvudregel,
You should set:
$col[“dbname”] = “o_huv.typ_1”;
Doing this will use o_huv.typ_1 in where clause.
