How to use datatable with a complex query?

webpointz

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

allan

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

webpointz

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.