Tabulator - Progressive Ajax Loading

Question

Tabulator: 4.9 - Google Chrome
I can't seem to get ajaxProgressiveLoad functioning as expected. (constructor is shown below).
The php file queries a SQL database and currently returns 107,000 records and is returned as a json:

echo(json_encode(["last_page"=>1, "data"=>$data]));

(A side question ref last_page - how is this determined when dealing with large datasets?)

The SQL query itself takes approx 1.5 seconds to return the data, but it seems as if the tabulator is waiting for all the data before displaying all the records after 20 seconds.

20:26:29.586 Callback: ajaxRequesting
20:26:51.059 Callback: ajaxResponse

This dataset is expected to grow and it would be preferable for the user to see some data immediatley or at least after a few seconds. Is there something i've missed in the constructor or on the php file?

Thanks in advance for any guidance offered Chris

var table = new Tabulator("#tabulator-table", {
    index:"Counter", 
    //initialSort:[{column:'Date', dir:'desc'}],
    height:(window.innerHeight - 100), 
    layout:"fitDataStretch",
    tooltips:true,
    tooltipsHeader:true,
    placeholder:"Please wait - retrieving data....",
    ajaxURL:"tabulatorSELECT_vMPS_Extract.php",
    ajaxParams:{CDSDealerCodes:strCDSDealerCodes},
    ajaxProgressiveLoad:"load",
    //ajaxProgressiveLoadDelay:200, //wait 200 milliseconds between each request
    ajaxRequesting:function(url, params){
        //url - the URL of the request
        //params - the parameters passed with the request
        console.log("Callback: ajaxRequesting");
    },
    ajaxResponse:function(url, params, response){
        //url - the URL of the request
        //params - the parameters passed with the request
        //response - the JSON object returned in the body of the response.
        console.log("Callback: ajaxResponse");
        return response; //return the response data to tabulator
    },
    headerFilterPlaceholder:"...",
    columns:[
        {title:'Date',             field:'Date',             headerFilter:'input', sorter:'datetime', bottomCalc:'count'},
        {title:'Amount',           field:'Amount',           headerFilter:'input', hozAlign:'right', formatter:"money", bottomCalc:"sum", bottomCalcParams:{formatter:"money"}, bottomCalcFormatter:"money"},
        {title:'Currency',         field:'Currency',         headerFilter:'input'},
        {title:'Programme',        field:'Programme',        headerFilter:'input'}
    ]
});

score 1 · Accepted Answer · answered Feb 04 '21 at 16:42

1

The point of the progressive ajax loading is that you DONT send all the data in one go which is exactly what you are doing there, which is why it is running slow.

You should paginate the data into chunks of say 20 rows at a time (the number of rows is up to you)

Tabulator will then request the data page by page as it is needed to be displayed, it tells you which chunk to return in the page param sent with the request.

the last_page property in the returned data should be the total number of available rows divided by the page size (rounded up)

answered Feb 04 '21 at 16:42

Oli Folkerd

7,510
1
22
46

Thanks for this info, it does make a bit more sense to me now, however, I still can't get it to function correctly. For test purposes, I have limited my select statement to the TOP 4000 records. I have added `paginationSize:100` to the table constructor and in the JSON I have set the `last_page` to 40. ...but when the tabulator loads I can see the first 4000 records and it then adds the same 4000 records a further 39 times giving me a total of 160,000 records (40 copies of the 4000) Is there something else I am missing? – Chris Cleary Feb 06 '21 at 15:50
That is because you have not set your backend to send the second pages of data. You are sending the first again. You need to look at the page that Tabulator is requesting and only return the correct data – Oli Folkerd Feb 06 '21 at 17:43
I'm using php and echoing the data as a json. Do i need to loop through and create smaller arrays and echo back 40 jsons? Is there a page on your website you could point me to which would help? `while ($record = $rs->fetchAssoc()){ $data[$i] = array('Counter' => $i,'Programme'=>$record['Programme']); $i = $i + 1; } echo(json_encode(["last_page"=>40,"data"=>$data]));` – Chris Cleary Feb 06 '21 at 21:23
The Tabulator website does not document server back ends because there are 100s of different servers and languages and ways to build it out and it sits outside the library. There should be no need to loop through anything in php you should be building an SQL query that only retrieves on page of records from the database at a time, working out the start record as the current page number minus one time the page size. This question tries to answer how to do that https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server – Oli Folkerd Feb 06 '21 at 23:40
1

Eureka!!! That's it! I was looking in the wrong place to try and fix this issue. This line fixed it for me `SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;` Thanks. – Chris Cleary Feb 07 '21 at 16:10

Tabulator - Progressive Ajax Loading

1 Answers1