This topic is locked

Dynamically Add Group By and/or HAVING clause to before sql query

11/28/2016 3:07:12 PM
PHPRunner Tips and Tricks
F
FunkDaddy author

Need to add the "GROUP BY" dd/or "HAVING" clause to a query dynamically?
Simply access the $pageObject in the before SQL query event and modify those arguments as follows:



$pageObject->gsqlGroupBy = " GROUP BY ProductID "; //Here we explicitly use the word GROUP BY

$pageObject->gsqlHaving = " FIND_IN_SET('" . $_GET['cat']. "', ProductID)";//Notice here we don't actually include word "HAVING"... we include only the statement that would come after it.


You could of course just alter the $strOrderBy variable available to you in list page events, however, if you include your GROUP BY or HAVING clauses in there, then columns will not sort correctly when you click on their headers. Hence to take advantage of the built in column sorts offered by phpr, use the pageObject to alter group by and having.
Cheers,
Marcelo

J
jomppa10 9/12/2017

Hi,
gsqlHaving or gsqlGroupBy are not valid in PHPRunner 9.8 anymore. When adding group by to $strOrderBy causes paging, sorting etc. stop working correctly. Does anyone know how to use dynamically group by in before sql query or after table initialized?
regards

Jouni