Datatables serverSide processing not working as intended, more like client side

horo

Hello!

I've encountered a problem with my datatables implementation. I'm currently trying to load a almost 50 000 results for my datatables and I used serverSide processing since client side takes a long time. I'm using datatables cdn 1.10.

Now as much as I understood, server side is used when I want to avoid loading all of the data at once, but it makes a new ajax request with each page call, and loads only the necessary data, reducing the load time significantly.

My problem is that with server side enabled, my datatables make a new request for each page, but they load ALL DATA every time and go into a timeout and die. I'm not sure where the problem is, so any help would be appreciated

This is how I use my datatables.
JS

    $('#my-table').DataTable({
        serverSide: true,
        processing: true,
        ajax: {
            url: '/user-comments',
            method: 'POST',
        },
        columns: [
            {   
                data: "rating",
                render: function ( data, type, full, meta ) {
                    if (full.rating == 1)
                        return 'LOVE';
                    else 
                        return 'HATE';
                }
            },
            {data: "comment"},
            {data: "email"},
        ],
        pageLength: 50,
        fnDrawCallback: function( ) {
            $('[data-toggle="tooltip"]').tooltip();
        },
        language: {
            processing: "Loading data...",
            emptyTable: "No data available!"
        }
    });

PHP

if ($this->request->isAjax()){
   $resultSet = Model::find();
   $array = [];
    foreach($resultSet as $res){
         //Note that this is an example of php usage, can't put the entire code do to security reasons, but the data provided to array is fine
          $array[] = $res->toArray();
     }
        $dataTables = new DataTable();
    $dataTables->fromArray($array)->sendResponse();
         $this->view->disable();
}

Thanks for the replies in advance!
Best regards.

bindrid

when serverSide is set to true, DataTables include a basic object in the ajax call as described here: https://datatables.net/manual/server-side It includes start and length which tells you where, out of the 50k rows where to start, and length, how many rows to grab from the start point

Its up to you and your sever side code to apply that information. Datatables does not do that for you server side

horo

Hmm, yes. I have solved my problem.

What I needed to do is break down my huge amount of records down to something more reasonable in loading time aspects. I was trying to break it with server-side handling since it makes new ajax calls for each page selected.

In case someone else runs into the same issue:

If you are processing a large amount of records and adding or modifying them, do not do it it first and pass the data to datatables. Give the datatables your entire result set with defined limit in Ajax ( Mine was 50), and use getResponse(), before you send it back. If you save it into a $response variable, you will have your handled set of data (only 50 in my case) and not the entire amount under $response['data'] and from there on you change what needs to be changed ( use foreach).

When the data has been modified pass it to datatables over $datatables->sendResponse($response); and change the sendResponse() function in datatable.php in a way that it takes modified data if it exists, instead of the normal response and your problem has been solved. Like this:

public function sendResponse($responseModified = NULL) {
                if ($this->di->has('view')) {
            $this->di->get('view')->disable();
        }

        $response = new Response();
        $response->setContentType('application/json', 'utf8');

        if($responseModified!=NULL){
            $response->setJsonContent($responseModified);
        } else {
            $response->setJsonContent($this->getResponse());
        }

        $response->send();
}

My 40000+ records datatable now loads each page in 1.5 seconds which is a great load time for the amount of data since now it works with only 50 needed records each time.

I hope this helps someone else aswell!