Datatables AJAX pagination with Search and Sort – Laravel 7

Last updated on by

DataTables is a jQuery library that is 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 7.

I am not using Laravel package for DataTables.

Datatables AJAX pagination with Search and Sort - Laravel 7

Contents

  1. Create Table
  2. Download DataTables library
  3. Model
  4. Route
  5. Controller
  6. View
  7. Conclusion

1. Create Table

  • Create a new table Employees using migration.
php artisan make:migration create_employees_table
  • Now, navigate to database/migrations/ folder from the project root.
  • Find a PHP file that 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();
    });
}
  • Run the migration –
php artisan migrate
  • The table is been created and add some records in it.

2. Download DataTables library

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

3. Model

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

Completed Code

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

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 App\Http\Controllers;

use Illuminate\Http\Request;
use App\Employees;

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

6. View

Create file –

  • Create a new folder employees at resources/views/ folder.
  • In resources/views/employees/ folder 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 7</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.6.0.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 -->
    <!-- <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/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.

You can view this tutorial to know DataTables AJAX pagination in Laravel 8.

If you found this tutorial helpful then don't forget to share.

© 2016-2024 Makitweb, All rights reserved