Custom query for server-side DataTables

MHSoftware

Hello, Server-Side DataTables are the only way to access very large amounts of data (lets say when you have more that 10,000 and up to some milions of rowsd).

But the only way to do it, is by specifying the table name... and I coulnd't find a way to refine that query (SELECT * FROM users) ... I would like it refined with my own query, something like (SELECT * FROM users WHERE activ=1) (showing only active users) and so on...

So how can I make server-side DataTables with my own queries.. thanks alot!

tangerine

There are plenty of posts about this if you search the forum. Basically you need to customise the server-side script, since any such example scripts in here are only intended as examples - not "one size fits all" production classes.

MHSoftware

That was the question, where to customize the query that receives the data from the server.. I mean the SQL sentence. I've searched, but haven't find anything explicit

allan

So how can I make server-side DataTables with my own queries.. thanks alot!

The details of how DataTables communicates with the server are documented in the manual. With that you can built a server-side processing script for any platform and with any queries you want.

Allan

MHSoftware

Thank you Allan. However, that is beyond my coding compatibilities. I only know basic php and mysql. I was hoping for a clue to modify the server-side script from the original example: https://www.datatables.net/examples/data_sources/server_side.html
I only uses the tables as a whole, how can I apply my own query?

allan

how can I apply my own query?

You can't really. You could certainly modify the code, but really it is designed and constructed to be an example script that will run the most basic of cases (80% of usage). It is designed to take column names and then dump the data that is required - it would likely need considerable modification to run custom queries (since it builds the query dynamically).

Allan

manuzed78

You can also cheat like this :
create a view exactly like you want.
server_side interogate the view

So you have your own request in your view.
a view is exactly the same as a table for datatables :D

allan

Ooo yes. I had forgotten about views. That is an excellent way of doing it. Good suggestion!

Allan

MHSoftware

Thank you very much for this suggestion, I will look upon it and let you know. Regards!

MHSoftware

Hello guys, I have found the simplest way to make my own queries. With the basic script provided (ssp.class.php)

All the examples come with the "simple" function used likes this:
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns );

but there is also the "complex" function that can specify your own conditions, like this:
SSP::complex ( $_GET, $sql_details, $table, $primaryKey, $columns, $whereResult="", $whereAll="id_store=".$id_store ) - Here I am filtering the results bu the StoreID.

Best Regards.