Search once and use data set on several tables on one page?

asle

I have a webpage with 3 tables showing order status. The tables all use the same data set. Total records are ca. 4000. The 3 tables show 1-not_finished, 2-finished and 3-canceled. I am not using server-side on this page since I understand there should be no speed isssue with 5000 records. Each record has 12 fields.

I have a page where I load the complete data set and that pages loading time is acceptable. But the problem page is this with 3 tables. So I wonder if I have missed something and is it possible to do 1 SQL call and use that returned data set in 3 places? The only difference between the tables are a field that filters not-finished, finished and canceled.

Is there a way to display (with filtering) the same data set from one search on 3 tables? It is just my thought that 3 times SQL search is what taking 3 times as long as 1 search.

For the record I am using EDITOR on the problem page and the one page with the complete data set that loads fast is only using datatables.js.

kthorngren

You haven't provided information telling us how you are currently loading the three tables and filtering the data. I'm guessing you are using ajax on all three tables to load the same dataset.

One option might be to pass the desired filter (not-finished, finished or canceled) using ajax.data and have the server just return the desired data for each table.

Another option might be to use jQuery ajax() to fetch the full dataset. in the success function you can populate the tables with the desired dataset. Either initialize each Datatable and use data to add the data or initialize empty Datatables, before the ajax call, and use rows.add() to populate the Datatables. You may want to restructure the response data into not-finished, finished and canceled datasets to populate the tables as appropriate.

Kevin

asle

Sorry, I should have been more clear. Here is the start of each code block for the 3 tables. As you see I do one ajax call for each table. I am not sure how I would use the ajax.data option to filter each table. Also I am lost about your second suggestion with the success function. Do you have an example to look at? How could I restructure the data sets on empty tables. Sorry I am knowledgeable enough to understand.

// Not finished orders
$('#datatable_notfinished').DataTable({
          processing: true,
          ajax: {
            url: '/editor-php/controllers/get_orders.php',
            type: "POST",
            data: function ( d ) {
                d.external_reg = 1;
                d.finish_status = 0;
                d.cancel = 0;
            }
        },.......

// Finished orders
$('#datatable_finished').DataTable({
          processing: true,
          ajax: {
            url: '/editor-php/controllers/get_orders.php',
            type: "POST",
            data: function ( d ) {
                d.external_reg = 1;
                d.finish_status = 1;
                d.cancel = 0;
            }
        },.......

// Canceled orders
$('#datatable_canceled').DataTable({
          processing: true,
          ajax: {
            url: '/editor-php/controllers/get_orders.php',
            type: "POST",
            data: function ( d ) {
                d.external_reg = 1;
                d.cancel = 1;
            }
        },.......

tangerine

I don't get why this requires three tables. Obviously I don't know all the details of your specific use case, but I would be looking at one table filterable by each of not-finished, finished and canceled.

kthorngren

I am not sure how I would use the ajax.data option to filter each table.

You are already passing parameters using ajax.data. You could add another for the data type to fetch, something like this:

          ajax: {
            url: '/editor-php/controllers/get_orders.php',
            type: "POST",
            data: function ( d ) {
                d.external_reg = 1;
                d.finish_status = 0;
                d.cancel = 0;
                d.data_type = 'not-finished`;
            }
        }

Your server script can then use this parameter to filter the query data it fetches from the DB. This way the server just responds with the data you want for that table.

How could I restructure the data sets on empty tables.

This example is for a different requirement:
https://live.datatables.net/huyexejo/1605/edit

It initializes Datatables using columns.data in the success function.

This is the same basic example but initializes an empty Datatable then uses -aop rows.add().
https://live.datatables.net/fudacuye/1/edit

Within the success function you would iterate the rows to separate the rows into different variables for each table. Having the server do this with a query would be more efficient.

Kevin

asle

Of course I could have one table and then filter on the 3 cases but I want to show 3 separate tables. This is on a admin page so the admin needs to see at a glance how many orders are in what categories

Not Finished Orders
.......
.......
.....

Finished Orders
.......
.......
.....

Canceled Orders
.......
.......
.....

Am I overthinking this? @tangerine you set me on an idea now. Maybe it would be more practical with one table and then i.ex. buttons for "Finished" "Not Finished" and "Canceled"? Default "Not Finished". I think I am at least have to test it out.

kthorngren

Yep its a good idea for one table. Maybe this checkbox search example will help
https://live.datatables.net/vipifute/421/edit

Kevin

kthorngren

If you have everything in one table you can also use the RowGroup to group the not-finished, finished and canceled items.

Kevin

asle

Thank you @kthorngren and @tangerine for helpful insight! So many good suggestions, so it can only get better! I liked the checkbox search example that could look nice with some styles buttons.

asle

@kthorngren you wrote

This is the same basic example but initializes an empty Datatable then uses -aop rows.add().
https://live.datatables.net/fudacuye/1/edit

But if I have 3 tables I would run the function 3 times and that would make the "/ssp/objects.php" run 3 times also. Am I correct? Not sure how this would be less load than what I already am doing when I am running the SQL query 3 times, one for every table. Now I am filtering at the quey level and not on the returned data.

I still think the approach with the filter buttons on one table seem smart. Working on that now.

kthorngren

But if I have 3 tables I would run the function 3 times

Just restructure the code so the ajax request is called once but populates all three tables, for example:
https://live.datatables.net/fudacuye/2/edit

Kevin