Laravel Yajra Datatable Server Side with pagination problems

Question

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 think that the individuals search keys are in $columns = $request->get('columns'); but I don't know how to manage count's query.

Thanks you for your precious advice.

HTML View Code:

<table id="oTable">
   <thead>
      <tr>
         <th>Action</th>
         <th>Brand</th>
         <th>Code</th>
         <th>Description</th>
      </tr> 
      <tr>
         <th class="no_search"></th>
         <th></th>
         <th></th>
         <th></th>
      </tr>
   </thead>
</table>

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');
      $columns = $request->get('columns');

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

Solution 1

 public function getRecords(Request $request) {
        //Use this way of your code
        $search  = $request->input('search.value');
        $columns = $request->get('columns');
        $order   = isset($_GET[ 'order' ]) ? $_GET[ 'order' ] : [];

        $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'
                    );
        foreach ($order as $o) {
            if(isset($columns[ $o[ 'column' ] ])) {
                $items = $items->orderBy($columns[ $o[ 'column' ] ][ 'name' ], $o[ 'dir' ]);
            }
        }
        $items = $items->take(10);

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

Solution 2

You Just need to replace the method inside controller and set the content as described below. It will solve your problems of

1. Managing the queries with or without search

2. Improve performance by enabling the pagination

   public function getRecords(Request $request) {
   
       $search = $request->input('search.value');
       $columns = $request->get('columns');
   
       $pageSize = ($request->length) ? $request->length : 10;
   
       $itemQuery = \DB::table('items')
       ->join('brands', 'items.brand', '=', 'brands.code');
   
       // $itemQuery->orderBy('items_id', 'asc');
       $itemCounter = $itemQuery->get();
       $count_total = $itemCounter->count();
   
       $count_filter = 0;
       if($search != ''){
           $itemQuery->where( 'brands.description' , 'LIKE' , '%'.$search.'%')
                   ->orWhere( 'items.description' , 'LIKE' , '%'.$search.'%')
                   ->orWhere( 'items.code' , 'LIKE' , '%'.$search.'%')
           $count_filter = $itemQuery->count();
       }
   
       $itemQuery->select(
           'items.id as items_id',
           'items.code as items_code',
           'items.description as items_description',
           'brands.description as brands_description'
       );
   
       $start = ($request->start) ? $request->start : 0;
       $itemQuery->skip($start)->take($pageSize);
       $items = $itemQuery->get();
   
       if($count_filter == 0){
           $count_filter = $count_total;
       }
   
       return Datatables::of($items)          
           ->with([
           "recordsTotal" => $count_total,
           "recordsFiltered" => $count_filter,
           ])
           ->rawColumns(['items_id','brands_description'])
           ->make(true);
   }