DataTables' ServerSide processing: Can I intercept the generated query?

eMaX commented on Feb 26

I'm having a query like select a, b, c from d; and I am using server side. I can make search work etc, but what I also want to do is display somewhere on my page the select count(distinct b) from d; but taking into account the filters that I've set for server side, but not using the pagination of the query.

So basically, I want to fire off a second query that takes in the first query that I've been using for the table, and then get out the number of distinct results for some column. I think I'll be able to parse the generated query; what I need is a way to get to the query as it is sent to the server.

Any ideas?

Thanks!

Sebastian Morales commented on Feb 27

Supposed you used DataTables' server side processing, you could catch its ajax request sending event like this:

    DataTables::create(array(
        'name' => 'DataTable0',
        ...
        "onReady" => "function() {
            DataTable0.on('xhr.dt', function (e, settings, json, xhr) {
                console.log('xhr.dt event: ', settings, json, xhr);
                // send a custom xhr request based on json to get server side information you want
                // and use the response to update your page
            });
        }"
    ));

eMaX commented on Mar 20

Thank you very much for your kind answer, Sebastian. I've tried that, but in the console I can then only essentially find the response, with the data, not the generated query. For the moment I'm good as I can monitor in my database logs, but it would be great to know if there's another option.

Sebastian Morales commented on Mar 21

Ah, I see your point. Unfortunately, the searching, sorting, and paging sql queries are built on server side and we can not catch them in a client side event. If you want the parameters DataTables sends to server side there's another event called "preXhr" like this:

    DataTables::create(array(
        'name' => 'DataTable0',
        ...
        "onReady" => "function() {
            DataTable0.on('preXhr.dt', function (e, settings, data) {
                console.log('parameters to be sent: ', data);
                ...
            });
            DataTable0.on('xhr.dt', function (e, settings, json, xhr) {
                console.log('xhr.dt event: ', settings, json, xhr);
                // send a custom xhr request based on json to get server side information you want
                // and use the response to update your page
            });
        }"
    ));

If you need any further information pls let us know.

KoolReport's Forum

DataTables' ServerSide processing: Can I intercept the generated query? #3254

Build Your Excellent Data Report