In this tutorial, I will show you how we can create AJAX pagination by search and sort using DataTables in Laravel.

Steps


Step-1: Create Table

  • Create a new table Employees using migration and add more records.

php artisan make:migration create_employees_table

  • Now, navigate to the database/migration/ directory from project roots.
  • Find the PHP file ending in create_employees_table and open it.
  • Describe the structure of the table in an 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();
    });
}

  • Run the migration-

php artisan migrate

  • A table has been created and added some records to it.


Step-2: Download

  • Download the DataTables library from here and download the jQuery library.
  • Export downloaded files public/directory.
  • Also, copy the jQuery library to the public/¬†directory.


Step-3: Model

  • Create Employees Model.

php artisan make:model Employees

  • Specify the Model attributes provided in bulk - username, name, and email using a $filliable property.

Completed Code

<?php

namespace App;

use IlluminateDatabaseEloquentModel;

class Employees extends Model
{

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

}
?>


Step-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 requests.


Step-5: Controller

Create EmployeesController Controller.

php artisan make:controller EmployeesController

Open app/Http/Controllers/EmployeesController.php file.

Import Employees Model.

Create two methods-

  • Index()
  • getEmployees()

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();
     
     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;
   }
}
?>


Step-6: View

  • Create new file
  • Include CSS and JS
  • HTML Table
  • Script

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="{{asset('DataTables/datatables.min.css')}}">

    <!-- Script -->
    <script src="{{asset('jquery-3.4.1.min.js')}}" type="text/javascript"></script>
    <script src="{{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 -->
    <!-- [removed][removed] -->

    <!-- Datatables JS CDN -->
    <!-- [removed][removed] -->

  </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>