SQL GROUP BY and HAVING clause on dhtmlxconnector

Hi there,

I want to ask about how to set the server side parameter to render the SQL query with GROUP BY and HAVING clause. Here’s the example of the query:

SELECT JBDTAPL,EXBEGDA,EXENDDA
,FLOOR(DATEDIFF(NOW(),PRBRDA)/365) AS AGE,PRMOBPH
,ROUND(SUM(DATEDIFF(DATE_FORMAT(IFNULL(EXENDDA,NOW()),‘%Y-%m-%d’),EXBEGDA))/365,1) AS EXTOTYR
FROM ipx_job_apply
LEFT JOIN ipx_app_person USING (ACCID)
LEFT JOIN ipx_app_exper USING (ACCID)
GROUP BY ACCID
HAVING EXTOTYR>5 AND AGE>30
ORDER BY JBDTAPL DESC,EXBEGDA DESC,EXENDDA DESC

How should I set the parameter for this query? because the standard filter using WHERE clause on #connector_text_filter cannot accommodate this query.

And if I used the GROUP BY clause in the sql statement like :

$sql = “SELECT * FROM table GROUP BY key1”;
$grid->render_sql($sql,“KEY1”,“FIELD1,FIELD2,FIELD2,FIELD4”);

They will return the wrong SQL statement after we do the filter action.

Please somebody gives me advice for this matter. Thank You.

If you plan to use the above sql for data loading only ( no updating, sorting, filtering ) the you can

  • grab latest connector
  • use render_complex_sql instead of render_sql

it will preserve sql code exactly as it was defined in the command

dhtmlx.com/x/download/regula … hp_dvl.zip

Really appreciate for the latest connector… :wink:

But, what if we need to use the HAVING clause like we want to filter in simple query? Isn’t there another way to do it? Maybe not to update the data, but sometimes we need it to sorting and filtering from the SQL which have the complex query, because this filter and sorting function is the strong and very good feature from dhtmlxgrid.

We used the dhtmlxgrid pro version, is there a way on this version to do this matter? Because I think the GROUP BY and HAVING clause is essential to support the limitation of WHERE clause in doing the filter for a condition refers to an aggregate function in SQL.

Please for your update and your advise. Thanks.

Connector will not be able to auto-build queries for such requests.
You can

a) create view in database which will hide complex sql construction , and user render_table with such view.

or

b) use render_complex_sql and create custom code handlers for update|sort|filter logic.