How to use datatable with a complex query?
How to use datatable with a complex query?
webpointz
Posts: 126Questions: 30Answers: 4
I have the following query which I converted into a MySQL View, however, when using the View in datatables and passing some where filters, it takes forever for a query to run. When the following query is run on its own it runs very fast.
My question then is, how can I use the following query in server side processing with datatables? Any help would be much appreciated.
SELECT
od.id AS id,
od.userid AS orderuserid,
cu.id AS customerid,
us.id AS userid,
concat(us.firstname, ' ', us.lastname) AS username,
cu.customername AS customername,
od.productid AS productid,
od.ordernumber AS ordernumber,
sum(od.quantity) AS qty_sum,
od.orderdate AS orderdate,
count(od.ordernumber) AS sub_sum
FROM
((table_orderdetails od
join table_customers cu)
join table_users us)
WHERE
(od.userid = us.id) and cu.id = { variable to be passed}
and date(orderdate) = '{variable to be passed}'
GROUP BY cu.customername , od.orderdate , od.userid , od.productid , od.quantity , us.username , od.productid , od.ordernumber
ORDER BY cu.customername , od.orderdate , od.userid , od.productid , od.quantity
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
You would need to write a script that implements the DataTables server-side processing protocol. The simple SSP demo script is just that, a demo script showing how it might be done (and follows the 80/20 rule - it will handle 80% of use cases I suspect).
For a complex query such as the above, it would require some custom coding.
Allan
Update: The query in the view was missing an added where statement for customerid which significantly improves performance as it now returns 75k rows as opposed to 754k.
My bad, but thanks for the reply.