0

I am loading a db table using Yajra datatables in Laravel 5.2. My table includes records above 50K, so the get() method takes a lot of memory. However, I use the paginate() method, an error occurs:

BadMethodCallException in Macroable.php line 74: Method getQuery does not exist.

Code is:

$required_orders = Order::where('order_status','Delivered')->paginate();
return Datatables::of($required_orders)->make(true);
Leigh
  • 28,765
  • 10
  • 55
  • 103
Ali Hamza
  • 97
  • 2
  • 9

1 Answers1

4

Datatables usually requires a QueryBuilder object, once you call paginate() you are basically working with a collection. You could use Datatables::collection(), but you don't want that as it requires you to fetch everything from the database, which you don't want to do.

Using yajra you should use the ajax calls to paginate, it will handle the pagination (not Laravel), hence it requires the QueryBuilder to perform counts etc. to return paginated data and metadata.

Best option would be to create a separate datatable (api) route, and check out datatables.net server side processing

Edit:

Following the example for server side processing you would want to do something like:

$(document).ready(function() {
    $('#yourDatatableId').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "/api/datatables/order"
    } );
} );

And let the route return something like:

$builder = Order::where('order_status', 'Delivered');

return Datatables::of($builder)->make(true);

For the html part, check out the example, basically you can define the columns in the javascript and it will fill in your rows from the data returned. Also checkout datatables server side manual, there you'll see start and length, which will be used for pagination by the frontend datatables lib.

Robert
  • 5,703
  • 2
  • 31
  • 32
  • Hi Robert! Thanks for your answer. Can you please suggest me a method to retrieve and list 100K records efficiently without consuming too much memory? – Ali Hamza Apr 14 '17 at 10:11
  • ! I have tried above mentioned procedure but issue is that when i run this command "$builder = Order::where('order_status', 'Delivered');" , it consumes a lot of memory as there are above 50K records. How can I overcome this issue in laravel 5.2? – Ali Hamza Apr 17 '17 at 03:11
  • @AliHamza Do you still call the builder in your controller action? You should just display the view with the empty table and let datatables fetch the 1st page async on the datatables route. – Robert Apr 19 '17 at 10:45
  • I've added the "draw": 1, "recordsTotal": 57, "recordsFiltered": 57, in ajax, but the datatable keeps processing, do you o know how to fix it? @Robert – swm Jan 29 '20 at 02:55
  • @swm without more info no. Also this answer was given 3 years ago, the library has changed since then. The draw param for example was not part of the interface – Robert Feb 04 '20 at 11:57