Why pagination is not working with Yajra data tables in Laravel 5.2

Question

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);

Answer 1

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.