How to get only first page of data and "request" on searchs and/or pagination?

DevGiu

I'm working on table with server-side enabled using laravel and datatables. My problem comes because data will come from a view with more than 300.000 records, and PHP runs out of memory. I would like to know how to setup datatables and communciation with server side, to retrieve only first page of data based on number of records per page, and how to requets again and pass needed params on pagination.

This is my JS code working like a charm iwth about 5000 records. on my firsts tests.

var table = $('#powersearch-table').DataTable({
           processing: true,
           serverSide: true,
           pageLength: 50,
           ajax: {
               url: "/powersearchajx",
               dataSrc: "data",
               type: "GET"
           },
           buttons: [
              'pageLength', 'csv', 'excel', 'pdf', 'print'
          ],
           columns: [
                  around 13 columns
           ],
       });

     $('#powersearch-table thead tr').clone(true).appendTo( '#powersearch-table thead' );
     $('#powersearch-table thead tr:eq(1) th').each( function (i) {
         var title = $(this).text();
         $(this).html( '<input type="text" placeholder="Search '+title+'" />' );

         $( 'input', this ).on( 'keyup change', function () {
             if ( table.column(i).search() !== this.value ) {
                 table
                     .column(i)
                     .search( this.value )
                     .draw();
             }
         } );
     } );

And this is my problematic server side code. Because returns all data, PHP runs out of memory.

return datatables(DB::table('myview'))->toJson();

Thanks in advance

tangerine

Your server-side code needs to apply the protocol described in the docs.
https://datatables.net/manual/server-side

DevGiu

Sorry, don't totally understand. I'm not sure exactly what to search there. I readed it and several pages with samples of people using it with so much records, but in all this samples, problem of people is speed (I mean, they don't have a problem retrieving and returning that 200k records, just is slow), and my problem is I can't retrieve all the data server side because my request runs out of memoy server side. I appreciate if possible more instructions about the "road" to take.

colin

If serverSide is enabled, your serve script should only return a page length of data, which in your case is 50 records, so it shouldn't be running out of memory.

Colin

tangerine

Your server-side code needs to apply the protocol described in the docs.
https://datatables.net/manual/server-side

I don't know how else to explain it.
Your server-side script is returning all your records because you are not supplying the required parameters, as explained in the docs.

kthorngren

As an example the server side request will have start and length parameters. You are expected to retrieve these and the other parameters and perform a data query that limits the data returned to the page being displayed. If you are using a SQL like DB then you will use LIMIT and OFFSET in your select query. More info here.

Then you are expected to process the searching and sorting parameters within your select query.

I'm not familiar with Laravel but this tutorial might help:
https://medium.com/justlaravel/how-to-implement-datatables-server-side-in-laravel-bcacf8472d70

Kevin

DevGiu

Will take a look, thanks to both.. Yes, but if you see in the sample, take all data from User object. I mean, all samples around, are just concept/tutorials about how to make it working, and with low data amount. But if table has a lot of records, this approach don't works because basically App\Data::all(); just takes the complete table in memory. For 100 records it's ok, for 15000 records maybe it's ok, but for 500k records obviously not

tangerine

If you look at the screenshot on your laravel link, you can see only ten rows displayed of 994 records. This conforms to the expected DataTables behaviour; it does not "take the complete table in memory".
"App\Data::all();" is qualified by the following line: "return Datatables::of($users)->make();"
I can't explain that to you as I don't know laravel. Have you actually applied that example to your own situation?

DevGiu

You see first 10 records, but Class::all() returns all and has nothing to do with Datatables, for this I ask, to know wich params I should pass. It's like do a "SELECT * FROM orders" and orders has 5 millions records. Sure datatables will show only first 10, but the query you are doing server side returns the complete table and for this reason PHP "crash".

kthorngren

With SQL type databases you would use LIMIT and OFFSET. How to do this with Laravel is a good question. If you are using a Laravel Datatables package you will be better off asking there. Or maybe on Stack Overflow. I don't believe there is anyone who answers questions regularly on this forum that is familiar with Laravel.

Kevin