Danny asked 9 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 9 years ago
This is not currently supported. I'll update you after some working.
Abu Ghufran answered 9 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 5 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 5 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 5 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 5 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.
