Overload setLoadMode() or dynamic_loading()?

Scheduler Javascript
#1

Here’s the gist of it: I have an enormously complicated query in my connector that ends in a ‘GROUP BY’ clause. Whenever I enable dynamic loading, the query fails. I think this is because the dynamic loading functions append the ‘WHERE BY’ clause to the very end of the SQL statement.

Is there a way to override this so the WHERE BY precedes the GROUP BY?

Thanks,
Chris

#2

And this is the (ugly) query:

21 $sql = 'SELECT s.id AS id, s.date AS date, s.end_date AS end_date, s.notes AS notes, '. 22 's.job_address AS job_address, s.van_id, s.phone_price as phone_price, '. 23 'v.name as van, v.id as van_id, '. 24 "concat(IF(ISNULL(c.first_name), '', c.first_name), IF(ISNULL(c.last_name), '', concat(' ',c.last_name)) ) as customer, ". 25 'a.service_id, '. 26 'GROUP_CONCAT(y.description separator ", ") as services '. 27 'FROM jos_che_sales s '. 28 'LEFT JOIN jos_che_customers c ON s.customer_id=c.id '. 29 'LEFT JOIN jos_che_vans v ON s.van_id=v.id ' . 30 'LEFT JOIN jos_che_appointments_services a ON s.id=a.appointment_id '. 31 'LEFT JOIN jos_che_services y ON a.service_id=y.id '. 32 'group by s.id '. 33 'order by s.id ';

#3

Instead of using render_sql you can create a view in database, based on your query and use render_table against that view.

#4

Hah. Works like a charm. Why didn’t I think of that :wink:

Thanks!
Chris

For anyone wandering in from google, since there is a ‘GROUP BY’ in the MySQL view, I also needed to keep the custom update code in the server-side code: $schConn->sql->attach("Update", $sql_update);

Where ‘$sql_update’ is the query to update the correct table.