0

I have faced problem regarding datatables.I am working in laravel and used yajra/laravel-datatables-oracle package.

In one of my product section, there are 100-200k records. When I hit my product listing, it will take lots of time to load because I am getting all records from the database.

I am trying to get records on the basis of limit but it will create a problem in pagination and searching. Only one pagination link working and all other pages return empty data.

Here below is my server side code:

$skip = $request->get('start') != null ? $request->get('start') : 0;
$take = $request->get('length') != null ? $request->get('length') : 10;
$products = Product::skip($skip)->take($take)->get();
$count = Product::get()->count();   
return Datatables::of($products)->setTotalRecords($count)->make(true);

ajax response:

{"draw":2,"recordsTotal":121,"recordsFiltered":121,"data":[],

Here data is empty, I am not sure how to append data while ajax call.

How I code when we have a large amount of records?

James Z
  • 12,209
  • 10
  • 24
  • 44
sunil
  • 1
  • 1
  • 6

2 Answers2

0

For handling large data you need to use yajra-datatables server side processing. in your javascript handle like this: 

<script>
$(document).ready(function() {
    $('.datatable').DataTable({
        processing: true,
        serverSide: true,
        ajax: '{{ route(' serverSide ') }}',
        columns: [
          {data: 'name', name: 'name'}
        ]
    });
});

then in route do something like this

    Route::get('/serverSide', function () {
        $products = Product::all();
        return Datatables::of($products)->make(true);
         })->name('serverside');

Edit:

You were returning collection rather than builder. check these issues in the github #575 and #1053

Shahriar Mohammad Nowshad
  • 26
  • 6
  • 1
    Hi, this is not working in my case because it will load all data from product table(Product::all()) . I need to fetch record on the basis of limit like Product::skip($skip)->take($take)->get(); – sunil Aug 12 '17 at 11:32
  • No, It won't load all data from product table. You should check your XHR request on console panel. Everytime you click on the pagination number it sends an ajax request and load data as per the page length. – Shahriar Mohammad Nowshad Aug 12 '17 at 13:15
  • 1
    On a quick note, you should use builder to get better result rather than using collection. you should get rid of get() method and setTotalRecords($count). – Shahriar Mohammad Nowshad Aug 12 '17 at 13:32
  • Please, check my answer edit. Hope that solves your problem. – Shahriar Mohammad Nowshad Aug 12 '17 at 13:39
  • Hi Nowshad, thanks for your reply.. You mean Product::all() will fetch records on the basis of start and end limit... – sunil Aug 14 '17 at 04:46
  • I know XHR request send start and page length but How i use this on the server side ? Can Product::all() will handle limit... – sunil Aug 14 '17 at 05:11
  • That was just a dummy code to explain. all() is a method for collection in laravel, if you use it your datatable will get slower as these issues i refered. You need to use a query that return a builder like DB::table('products')->get(). – Shahriar Mohammad Nowshad Aug 15 '17 at 12:12
  • Sorry ,i am not getting you...Can you please let me know how i set start and end limit . I think get() return all records and the query result is slower. – sunil Aug 16 '17 at 05:10
  • Same problem, i have exactly your code where my 'products' are 15 millions rows and it load all at once. It will render only 15 but it loads the entire query, you need to do something specific where you limit your query and show based on the page. – hugo411 Oct 14 '20 at 18:11
0

Pass a variable name length with the value of your limit. Ex.:

$('#tasks-table').on('preXhr.dt', function (e, settings, data) {
        data['employee'] = $('#employeeId').val();
        data['length'] = 20; //This is the pagination limit data variable
});
Hossain Mohammad Imran
  • 1
  • 3