Server-Side DataTables Rendering in Laravel

#datatables #performance #php #laravel

Aside from doing indexes, and use join() instead of with(). This example uses the DataTables library.

<link rel="stylesheet" href="https://cdn.datatables.net/2.3.7/css/dataTables.dataTables.css" />  
<script src="https://cdn.datatables.net/2.3.7/js/dataTables.js"></script>

1. Route

Route::post('/data', [App\Http\Controllers\MyController::class, 'datatable'])->name('mydata.datatable');

2. View

Date input, datatable, and button.

<div class="mb-4 form-group col-lg-4">
    <label class="form-label">Start Date</label>
    <input type="text" name="start_date" id="start_date" class="form-control" required>
</div>
<div class="mb-4 form-group col-lg-4">
    <label class="form-label">End Date</label>
    <input type="text" name="end_date" id="end_date" class="form-control" required>
</div>
<div class="mb-4 form-group col-lg-4">
    <button class="btn btn-sm btn-light-primary font-weight-bold" id="btnShow">Show</button>
</div>

<table class="table table-hover table-bordered table-vertical-center" id="tableMyData">
    <thead class="table-success font-weight-bold">
        <tr>
            <th class='text-left'>INVOICE</th>
            <th class='text-left'>AMOUNT</th>
        </tr>
    </thead>
    <tbody style="white-space: nowrap;"></tbody>
</table>

<script>
  $('#btnTampilkan').on('click', function() {
      _loadMyData();
  });

  let table;
  function _loadMyData() {
      var start = $('#start_date').val(); 
      var end = $('#end_date').val(); 
      if (table) table.destroy();
      if (!start || !end) return Swal.fire('Warning', 'Start & End date is required!', 'warning');
      table = $('#tableMyData').DataTable({
          processing: true,
          autoWidth: false,
          serverSide: true,
          pagingType: 'full_numbers',
          lengthMenu: [[10, 20, 50, 100], [10, 20, 50, 100]],
          pageLength: 10,
          destroy: true,
          ajax: {
              url: "{{ route('mydata.datatable') }}",
              type: 'POST',
              data: {
                  start_date: start,
                  end_date: end,
                  _token: "{{ csrf_token() }}"
              }
          },
          language: {
              emptyTable: "No data found"
          },
          order: [[1, 'desc']],
          columnDefs: [
              { orderable: false, targets: 0, width: '3%' },
          ],
          columns: [
              {
                  data: null,
                  render: (data) => `<input type="checkbox" class="row-checkbox" data-nosep="${data.nosep}">`
              },
              { data: 'invoice' },
              { data: 'amount' },
          ],
          // search delay
          initComplete() {
              const api = this.api();
              let typingTimer;
              $('#tableMyData_filter input').off().on('keyup', function (e) {
                  clearTimeout(typingTimer);
                  const value = this.value;
                  if (e.which === 13) {
                      api.search(value).draw();
                  } else {
                      typingTimer = setTimeout(() => api.search(value).draw(), 500);
                  }
              });
          }
      });
  }
</script>

3. Controller

use Illuminate\Http\Request;

public function datatable(Request $request)
{
    $query = ...
        ->whereBetween('order_date', [$request->start, $request->end]);

    $maxLimit = 100000;
    $columns = [null, 'invoice', 'amount'];

    $recordsTotal = min((clone $query)->count(), $maxLimit);
    // SEARCH
    if ($search = $request->input('search.value')) {
        $normalized = str_replace(['.', ','], '', $search);
        $query->where(function($q) use ($search, $normalized) {
            $q->where('invoice', 'LIKE', "%{$search}%")
              ->orWhere('amount', 'LIKE', "%{$search}%");
        });
    }

    $recordsFiltered = min((clone $query)->count(), $maxLimit);

    // ORDERING
    $order = $request->input('order', []);
    if ($order && ($col = $columns[$order[0]['column']] ?? null)) {
        $query->orderBy($col, $order[0]['dir'] ?? 'ASC');
    } else {
        $query->orderBy('order_date', 'ASC');
    }

    // PAGINATION
    $start = $request->input('start');
    $results = $start >= $maxLimit
        ? collect()
        : $query->offset($start)
                ->limit(min($request->input('length'), $maxLimit - $start))
                ->get();

    return response()->json([
        'draw' => intval($request->draw),
        'recordsTotal' => $recordsTotal,
        'recordsFiltered' => $recordsFiltered,
        'data' => $results->map(fn($r) => [
            'invoice' => $r->invoice,
            'amount' => $r->amount,
        ])
    ]);
}