Datatables AJAX pagination with Search and Sort – Laravel

1596833427 Datatables AJAX pagination with Search and Sort Laravel e1597414595274

DataTables is a jQuery library that used to create pagination. It comes with various features – pagination, sorting, search, etc.

You can load data with and without AJAX.

In this tutorial, I show how you can create AJAX pagination with search and sort using DataTables in Laravel.
I am not using Laravel package for DataTables.


1. Create Table

  • Create a new table Employees using migration and add some records.
php artisan make:migration create_employees_table
  • Now, navigate to database/migration/ directory from the project root.
  • Find a PHP file which ends with create_employees_table and open it.
  • Define the table structure in the up() method.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
       $table->bigIncrements('id');
       $table->string('username');
       $table->string('name');
       $table->string('email');
       $table->timestamps();
    });
}
php artisan migrate
  • The table is been created and add some records in it.

2. Download

  • Download DataTables library from here and also download the jQuery library.
  • Extract the downloaded files in public/ directory.
  • Also, copied jQuery library in public/ directory.

3. Model

php artisan make:model Employees
  • Specify mass assignable Model attributes – name, and email using the $filliable property.

Completed Code

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class Employees extends Model
{

   protected $fillable = [
      'username','name','email' 
   ];

}

4. Route

  • Open routes/web.php file.
  • Define 2 routes –
Route::get('/','EmployeesController@index');
Route::get('/employees/getEmployees/','EmployeesController@getEmployees')->name('employees.getEmployees');
  • The 2nd route is used for AJAX request.

5. Controller

  • Create EmployeesController Controller.
php artisan make:controller EmployeesController
  • Open app/Http/Controllers/EmployeesController.php file.
  • Import Employees Model.
  • Create two methods –
    • index() – Load employees.index view.
    • getEmployees() – This use to handle DataTable AJAX request.

Read Datatables sent values and store in the variables.

Count total records with and without search filter and assign in the variables.

Fetch records from employees table. Use $searchValue to search on name field, $start in skip() and $rowperpage in take() to limit the number of records fetched.

Loop on the fetched records and initialize $data_arr Array with a similar key as defined in columns option while DataTables initialization.

Initialize $response Array with draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.

Return $response Array in JSON format.

Completed Code

<?php

namespace AppHttpControllers;

use IlluminateHttpRequest;
use AppEmployees;

class EmployeesController extends Controller{

   public function index(){
      return view('employees.index'); 
   }

   /*
   AJAX request
   */
   public function getEmployees(Request $request){

     ## Read value
     $draw = $request->get('draw');
     $start = $request->get("start");
     $rowperpage = $request->get("length"); // Rows display per page

     $columnIndex_arr = $request->get('order');
     $columnName_arr = $request->get('columns');
     $order_arr = $request->get('order');
     $search_arr = $request->get('search');

     $columnIndex = $columnIndex_arr[0]['column']; // Column index
     $columnName = $columnName_arr[$columnIndex]['data']; // Column name
     $columnSortOrder = $order_arr[0]['dir']; // asc or desc
     $searchValue = $search_arr['value']; // Search value

     // Total records
     $totalRecords = Employees::select('count(*) as allcount')->count();
     $totalRecordswithFilter = Employees::select('count(*) as allcount')->where('name', 'like', '%' .$searchValue . '%')->count();

     // Fetch records
     $records = Employees::orderBy($columnName,$columnSortOrder)
       ->where('employees.name', 'like', '%' .$searchValue . '%')
       ->select('employees.*')
       ->skip($start)
       ->take($rowperpage)
       ->get();

     $data_arr = array();
     $sno = $start+1;
     foreach($records as $record){
        $id = $record->id;
        $username = $record->username;
        $name = $record->name;
        $email = $record->email;

        $data_arr[] = array(
          "id" => $id,
          "username" => $username,
          "name" => $name,
          "email" => $email
        );
     }

     $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecords,
        "iTotalDisplayRecords" => $totalRecordswithFilter,
        "aaData" => $data_arr
     );

     echo json_encode($response);
     exit;
   }
}

6. View

Create file –

  • Create a new directory employees at resources/views/ directory.
  • In resources/views/employees/ directory create a new index.blade.php file.

Include CSS and JS –

  • Include Datatables CSS, jQuery, and Datatables JS at the <head > section.

HTML Table –

  • Create <table id="empTable">.

Script –

  • Initialize DataTable on #empTable.
  • Set processing: true, serverSide: true.
  • Send AJAX request to "{{route('employees.getEmployees')}}".
  • In the columns options specify the key names which get read on successful callback.

Completed Code

<!DOCTYPE html>
<html>
  <head>
    <title>Datatables AJAX pagination with Search and Sort - Laravel</title>

    <!-- Meta -->
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <meta charset="utf-8">
   
    <!-- CSS -->
    <link rel="stylesheet" type="text/css" href="https://www.phpsof.com/datatables-ajax-pagination-with-search-and-sort-laravel/{{asset("DataTables/datatables.min.css')}}">

    <!-- Script -->
    <script src="https://www.phpsof.com/datatables-ajax-pagination-with-search-and-sort-laravel/{{asset("jquery-3.4.1.min.js')}}" type="text/javascript"></script>
    <script src="https://www.phpsof.com/datatables-ajax-pagination-with-search-and-sort-laravel/{{asset("DataTables/datatables.min.js')}}" type="text/javascript"></script>


    <!-- Datatables CSS CDN -->
    <!-- <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css"> -->

    <!-- jQuery CDN -->
    <!-- <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> -->

    <!-- Datatables JS CDN -->
    <!-- <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script> -->

  </head>
  <body>

    <table id='empTable' width="100%" border="1" style="border-collapse: collapse;">
      <thead>
        <tr>
          <td>S.no</td>
          <td>Username</td>
          <td>Name</td>
          <td>Email</td>
        </tr>
      </thead>
    </table>

    <!-- Script -->
    <script type="text/javascript">
    $(document).ready(function(){

      // DataTable
      $('#empTable').DataTable({
         processing: true,
         serverSide: true,
         ajax: "{{route('employees.getEmployees')}}",
         columns: [
            { data: 'id' },
            { data: 'username' },
            { data: 'name' },
            { data: 'email' },
         ]
      });

    });
    </script>
  </body>
</html>

7. Conclusion

Initialize DataTables in your view file and handle DataTables AJAX request from the controller. DataTables response must be in the specified format.

In case if your data is not loading on the DataTable then debug it from the controller. Check if all key names specified in the ‘columns’ option in datatables initialize are defined or not, check the SQL query.

Leave a Reply

Your email address will not be published. Required fields are marked *