Laravel Yajra Datatable Server Side with pagination problem

[robertoremondiniarcadia]

I'm newby on Laravel and I'm trying to use Yajra Datatable Plugin with server side funtionality. The plugin works well with a small amount of records, but I have a large amount of about 100000 record.

To speed up the process in my controller I limit the query's result with take(10) and I use another query to count the total results. So far everything is fine.

The problem is how to manage research. In addition to the main research field, i used also the individual column searching but I don't know how to return the correct numbers of record to manage the pagination with the individual searching filter.

I need to catch the individuals search keys but I don't know where are passed at datatable and how to manage count's query.

Thanks you for your precious advice.

HTML View Code:

Action	Brand	Code	Description

Jquery Code:

$('#oTable').DataTable({ dom: 'lfrtip', "processing": true, "serverSide": true, "ajax": '{!! url('getRecords') !!}', "columns": [ {data: 'items.id', name: 'items_id'}, {data: 'brands.description', name: 'brands_description'}, {data: 'items.code', name: 'items_code'}, {data: 'items.description', name: 'items_description'} ], columnDefs: [ {targets: 'no_sort', orderable: false} ], initComplete: function () {

  this.api().columns().every(function () {
    var column = this;
    var columnClass = column.header().className;
    if (columnClass.indexOf('no_search') != false) {
      var input = document.createElement("input");
      $(input).addClass('form-control');
      $(input).appendTo($(column.header()).empty())
      .on('change', function () {
        column.search($(this).val(), false, false, true).draw();
      });
    }
  });
}

}); Controller's method:

public function getRecords(Request $request) {

  $search = $request->input('search.value');

  $count_total = \DB::table('items')
                    ->join('brands', 'item.brand', '=', 'brands.code')
                    ->count();

  $count_filter = \DB::table('items')
                    ->join('brands', 'items.brand', '=', 'brands.code')
                    ->where(   'brands.description' , 'LIKE' , '%'.$search.'%')
                    ->orWhere( 'items.description' , 'LIKE' , '%'.$search.'%')
                    ->orWhere( 'items.code' , 'LIKE' , '%'.$search.'%')
                    ->count();

  $items= \DB::table('items')
    ->join('brands', 'items.brand', '=', 'brands.code')
    ->select(
        'items.id as items_id',
        'items.code as items_code',
        'items.description as items_description',
        'brands.description as brands_description'
    ) -> take(10);

    return Datatables::of($items)          
      ->with([
        "recordsTotal" => $count_total,
        "recordsFiltered" => $count_filter,
      ])
      ->rawColumns(['items_id','brands_description'])
      ->make(true);
}

[yajra]

You don't need to count the records and remove the take query. All of this are being handled by the plugin. Your code should be something like:

$items= \DB::table('items')
    ->join('brands', 'items.brand', '=', 'brands.code')
    ->select(
        'items.id as items_id',
        'items.code as items_code',
        'items.description as items_description',
        'brands.description as brands_description'
    );

    return Datatables::of($items)
      ->rawColumns(['items_id','brands_description'])
      ->make(true);

[robertoremondiniarcadia]

Hi Yajra, first of all I would like to thank you for answering so fast and your excellent works.

Before loading over 100,000 records into the database, my code was exactly like the one you just wrote.

Unfortunately, after loading it became too slow, because the query actually joins the two tables and extracts all 100000 records before passing them to the function of creating the Datatable.

With the changes I've made, it's fast enough, but I'd like to understand how to better manage individual search fields in getRecords() function.

Do you have any other ideas to speed up and limit database queries?

Thank you, Roberto

[yajra]

Have you tried adding some indexes on your table and reviewed some of the queries generated? Try inspecting the ajax request with APP_DEBUG=true to see the queries. Afterwards, try executing it manually or inspect which query is running slow. We have a table with millions of records and is responding fast enough with proper index in place with limited searchable fields.

On the other hand, see this PR https://github.com/yajra/laravel-datatables/pull/1338 for some possible workaround to override the total records and implement a custom paging.

[swati-clarion]

Hi Yajra,

I have same issue. I am not using Laravel Eloquent query as it was not producing proper results. So, i added plain mysql query like:

$qryRec = "SELECT r.id, r.reception_number, r.year, r.instrument_type, r.book, r.page, t1.grantor, t2.grantee FROM tblreception AS r LEFT JOIN (SELECT name_information AS grantor , reception_id FROM tblreception_information t1 WHERE code='R' ORDER BY t1.id ASC) AS t1 ON r.id = t1.reception_id LEFT JOIN (SELECT name_information AS grantee , reception_id FROM tblreception_information t2 WHERE code='E' ORDER BY t2.id ASC) AS t2 ON r.id = t2.reception_id WHERE 1 AND r.year = '2016' GROUP BY r.reception_number ORDER BY r.book"; $reception = DB::select($qryRec); return Datatables::of($reception)->make(true);

This query takes around approx 2.3 seconds to execute in phpmyadmin but datatables is taking around 20-30 seconds to load. I have nearly 270607 records in tblreception_information table.

Here is my blade file script for datatables:

$(document).ready(function() {

 var oTable = $('.datatable').DataTable({
    dom: "<'row'<'col-xs-12'<'col-xs-6'l><'col-xs-12'p>>r>"+
        "<'row'<'col-xs-12't>>"+
        "<'row'<'col-xs-12'<'col-xs-6'i><'col-xs-6'p>>>",		
    processing: true,
    serverSide: true,
	ordering: true,
	pageLength: 50,
	deferRender: true,	
	language: {
		emptyTable: "Please enter Year or Reception Number or Name to view records.",
		/* infoEmpty: "No entries to show", */
	},
	contentType: "application/json; charset=utf-8",
	order: [[ 1, "asc" ]],
    ajax: {
        url: '{{ route('reception/custom-filter-data') }}',
        data: function (d) {
			
            d.year = $('input[name=year]').val();
            d.number = $('input[name=reception_number]').val();
			d.name = $('input[name=name_information]').val();
			d.type = $('#instrument_type').val();
			d.book = $('input[name=book]').val();
			d.page = $('input[name=page]').val();							
        } 
    },	   
   columns: [            
        {data: 'year', name: 'year'},
        {data: 'reception_number', name: 'reception_number'},			
		//{data: 'grantor', name: 'grantor'},
		{ 
			'data': 'grantor',
			 'render': function(data, type, row, meta) {
				if (type === 'display'){
					
					data = '<a id="test">' + data + '</a>';
				}
				return data;
			 }
		},			
		{data: 'grantee', name: 'grantee'},				
		{ 
			'data': 'book',
			 'render': function(data, type, row, meta) {
				if (type === 'display'){
					data = '<a href="#">' + data + '</a>';
				}
				return data;
			 }
		}, 				
		{ 
			'data': 'page',
			 'render': function(data, type, row, meta) {
				if (type === 'display'){
					data = '<a href="#">' + data + '</a>';
				}
				return data;
			 }
		},
		{data: 'instrument_type', name: 'instrument_type'},		
										
    ]
});	

Please suggest how to fix slow processing issue.

[yajra]

@swati-clarion you are using collection $reception = DB::select($qryRec);. Use query builder for better performance.

$reception = DB::table('receptions')->join(...)...

[swati-clarion]

Hi Yajra,

As suggested , I updated query as below. Still it takes around 15-20 seconds to load page:

$reception = DB::table('tblreception')->select(
				'tblreception.id','year','reception_number','instrument_type','book','page',
				't1.grantor','t2.grantee'
			)->leftJoin(
				DB::raw("
					(SELECT name_information AS grantor , reception_id FROM tblreception_information t1 WHERE code='R' ORDER BY t1.id ASC)
				`t1` "),'tblreception.id', '=', 't1.reception_id'
			)->leftJoin(
				DB::raw("
					(SELECT name_information AS grantee , reception_id FROM tblreception_information t2 WHERE code='R' ORDER BY t2.id ASC)
				`t2` "),'tblreception.id', '=', 't2.reception_id'
			)->groupBy('tblreception.reception_number')
			->orderBy('tblreception.book'); 
			
			return Datatables::of($reception)
			
				->filter(function ($query) use ($request) {
					
					if ($request->has('name') && $request->get('name') != '') {
						$query->where('t1.grantor', 'like', "%{$request->get('name')}%")->orWhere('t2.grantee', 'like', "%{$request->get('name')}%");
					}

					if ($request->has('year') && $request->get('year') != '') {
						$query->where('year', 'like', "%{$request->get('year')}%");
					}
					
					if ($request->has('number') && $request->get('number') != '') {
						$query->where('reception_number', '>=', "{$request->get('number')}");
						$query->orderBy('reception_number');
					}
					
					if ($request->has('type') && $request->get('type') != '') {
						$query->where('instrument_type', 'like', "%{$request->get('type')}%");
					} 
					
					if ($request->has('book') && $request->get('book') != '') {
						$query->where('book', '=', "{$request->get('book')}");
					}
					
					if ($request->has('page') && $request->get('page') != '') {
						$query->where('page', '=', "{$request->get('page')}");
					}
							
				})
			 
            ->make(true); 

[carlosmds]

Hi guys, i was facing the same issue with server-side pagination directly from QueryBuilder. About this, two things:

1 - Workaround

Add this code before you return the Datatables::of():

$request->merge(['start' => 0]); or Datatables::of($data)->skipPaging() method.

2 - Why?

The problem here is because the package manages the limit and offset of the data automatically by default. In another words, it's something like "just do the query and let the pagination with me". It is a great package, really. Although, there is an issue when handling big and slow queries in which the pagination needs to be done directly from the query for better performance. The Datatables lib uses our request to handle this pagination, with $request->start and $request->length. So, the lib receives as parameter an array or collection, and apply that start (offset) and length(limit) directly in the data it was given.

For example, if my query has a limit of 10 and an offset of 30, when the data array/collection is passed to Datatables it will apply the same filter of limit 10 and offset 30, but the data of the query has only 10 rows. That means it will return nothing, because the number of results of the query is less than the offset value.

Hope it helps someone!

[tornadofay]

Hi guys, i was facing the same issue with server-side pagination directly from QueryBuilder. About this, two things:

1 - Workaround

Add this code before you return the Datatables::of():

$request->merge(['start' => 0]); or Datatables::of($data)->skipPaging() method.

2 - Why?

The problem here is because the package manages the limit and offset of the data automatically by default. In another words, it's something like "just do the query and let the pagination with me". It is a great package, really. Although, there is an issue when handling big and slow queries in which the pagination needs to be done directly from the query for better performance. The Datatables lib uses our request to handle this pagination, with $request->start and $request->length. So, the lib receives as parameter an array or collection, and apply that start (offset) and length(limit) directly in the data it was given.

For example, if my query has a limit of 10 and an offset of 30, when the data array/collection is passed to Datatables it will apply the same filter of limit 10 and offset 30, but the data of the query has only 10 rows. That means it will return nothing, because the number of results of the query is less than the offset value.

Hope it helps someone!

thank you so much, I've been running in circles for the past 10 hours trying to figure out why my pages have no data and just adding ->skipPaging() solved my problem.

[ruslanashaari]

Hi guys, i was facing the same issue with server-side pagination directly from QueryBuilder. About this, two things:

1 - Workaround

Add this code before you return the Datatables::of():

$request->merge(['start' => 0]); or Datatables::of($data)->skipPaging() method.

2 - Why?

The problem here is because the package manages the limit and offset of the data automatically by default. In another words, it's something like "just do the query and let the pagination with me". It is a great package, really. Although, there is an issue when handling big and slow queries in which the pagination needs to be done directly from the query for better performance. The Datatables lib uses our request to handle this pagination, with $request->start and $request->length. So, the lib receives as parameter an array or collection, and apply that start (offset) and length(limit) directly in the data it was given.

For example, if my query has a limit of 10 and an offset of 30, when the data array/collection is passed to Datatables it will apply the same filter of limit 10 and offset 30, but the data of the query has only 10 rows. That means it will return nothing, because the number of results of the query is less than the offset value.

Hope it helps someone!

Thanks mate! I've been wondering why the datatable didnt display any data from the 2nd page and so on. Add skipPaging() to it, it works magnificiently.

[temeriamos]

Hi guys, i was facing the same issue with server-side pagination directly from QueryBuilder. About this, two things:

1 - Workaround

Add this code before you return the Datatables::of():

$request->merge(['start' => 0]); or Datatables::of($data)->skipPaging() method.

2 - Why?

The problem here is because the package manages the limit and offset of the data automatically by default. In another words, it's something like "just do the query and let the pagination with me". It is a great package, really. Although, there is an issue when handling big and slow queries in which the pagination needs to be done directly from the query for better performance. The Datatables lib uses our request to handle this pagination, with $request->start and $request->length. So, the lib receives as parameter an array or collection, and apply that start (offset) and length(limit) directly in the data it was given.

For example, if my query has a limit of 10 and an offset of 30, when the data array/collection is passed to Datatables it will apply the same filter of limit 10 and offset 30, but the data of the query has only 10 rows. That means it will return nothing, because the number of results of the query is less than the offset value.

Hope it helps someone!

Excelente.. me sirvió un saludo.

[lamhuynhit]

You should try it. return Datatables::of($items)
->with([ "recordsTotal" => $count_total, "recordsFiltered" => $count_filter, ]) ->rawColumns(['items_id','brands_description']) ->setTotalRecords($count_total) ->setFilteredRecords($count_filter) ->skipPaging() ->make(true);

Hope it help.

[MustakimMansuri6040]

Hello Everyone, You can try below code Its working fine for the server-side pagination and get only page-wise record But you need to work for the filter and sorting.

if ($request->ajax()) { $col_arr = ['id','name','email','phone_no','created_at']; DB::enableQueryLog(); $data_obj = User::where('role_id',2)->whereNull('deleted_at'); $total_count = $data_obj->count(); if(isset($request->search['value']) && $request->search['value'] != '') { $search_val = $request->search['value']; $data_obj->where(function($query) use ($search_val, $col_arr) { foreach($col_arr as $val) { $query->orWhere($val, 'like', "%$search_val%"); } }); } $filter_count = $data_obj->count(); if(isset($request->start)) { $data_obj->offset($request->start); } if(isset($request->length)) { $data_obj->limit($request->length); } $col_order = $col_arr[$request->order[0]['column']] ?? 'id'; $sort_order = $request->order[0]['dir'] ?? 'desc'; $col_arr[] = 'encryption_id'; $data_obj = $data_obj->select($col_arr)->orderBy($col_order, $sort_order); $data = $data_obj->get(); $query = DB::getQueryLog(); // print_r($query); return Datatables::of($data) ->with([ "recordsTotal" =>$total_count, "recordsFiltered" => $filter_count, ]) ->rawColumns(['action']) ->setTotalRecords($total_count) ->setFilteredRecords($filter_count) ->addIndexColumn() ->editColumn('created_at', function ($data) { return $data->created_at; }) ->addColumn('action', function($row){ $edit_url = route('users.create',[$row->encryption_id]); $delete_url = route('users.delete',[$row->encryption_id]); // View $btn = ' Edit Delete'; return $btn; }) ->skipPaging() ->make(true); }

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.