Datatables AJAX pagination with Search and Sort in Laravel 10

Last updated on by

In today’s web applications, it is e­ssential to present e­xtensive sets of data in a way that e­ngages and invites interaction with the­ user. AJAX-based pagination, search, and sorting te­chniques are valuable re­sources when trying to improve navigation for your use­rs.

These methods allow visitors to move­ efficiently through data tables without inte­rruptions or delays caused by reloading the­ entire page.

In this article, we will explore the process of implementing AJAX pagination with search and sort functionality using DataTables library in Laravel 10 without relying on external packages like Yajra.

DataTables AJAX pagination with Search and Sort in Laravel

Demo

Table of Content

  1. Creating a Table using Migration
  2. Download DataTables library
  3. Create a Model
  4. Define Routes
  5. Create Controller – Load DataTable data
  6. Creating the View – Initialize DataTable
  7. Demo
  8. Conclusion

1. Creating a Table using Migration

  • Run the following command to generate a migration file for the employees table:
php artisan make:migration create_employees_table
  • This command will create a new migration file in the database/migrations folder.
  • Open the migration file and define the table columns in up() method.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->timestamps();
    });
}
  • Once defined the table columns, run the migration command to create the employees table in the database:
php artisan migrate

2. Download DataTables library

  • Download DataTables library from the official website and also download the jQuery library.
  • Extract the downloaded files in public/ folder and also, copied the jQuery library in public/ folder.
  • You can also use CDN –
<!-- Datatable CSS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css"/>

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

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>

3. Create a Model

  • Run the following command to generate the Employees model:
php artisan make:model Employees
  • This command will create a new model file Employees.php in the app/Models location.
  • Modify the $fillable array to include the attributes that correspond to the columns in the employees table. This will allow us to mass assign values to these attributes when creating or updating user records.
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

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

4. Define Routes

  • Open routes/web.php file.
  • Define 2 routes –
    • / – Load index view.
    • /getEmployees – This is used to send AJAX POST request to fetch the datatables data.
<?php
use Illuminate\Support\Facades\Route; 
use App\Http\Controllers\EmployeesController; 

Route::get('/', [EmployeesController::class, 'index']); 
Route::get('/getEmployees', [EmployeesController::class, 'getEmployees'])->name('getEmployees');

5. Create Controller – Load DataTable data

  • Create EmployeesController Controller by running the following command:
php artisan make:controller EmployeesController
  • This command will generate a new controller file EmployeesController.php in the app/Http/Controllers folder.

Here, create 2 methods –

  • index() – This method is responsible for loading the index view.
  • getEmployees() – This method handles the AJAX request from DataTables.

In this method, extract the values sent by DataTables and assign them to respective variables.

Next, count the total number of records in the employees table, both with and without the search filter applied. These counts will be assigned to the variables $totalRecords and $totalRecordsWithFilter.

After that, fetch the records from the employees table and assign them to the $records variable.

To prepare the data for DataTables, loop through the fetched records and initialize an array named $data. The keys of this array will correspond to the columns specified in the columns option when initializing DataTables.

Finally, initialize the $response array with the required values and return it in JSON format.

By following these steps, you will be able to handle the DataTables AJAX request, fetch and process the necessary data from the employees table, and provide the response in the expected format for DataTables to display the paginated, searched, and sorted results.

<?php

namespace App\Http\Controllers;

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

class EmployeesController extends Controller
{
     public function index(){

         // Load index view
         return view('index');
     }

     // Fetch records
     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
        );

        return response()->json($response); 
     }
}

6. Creating the View – Initialize DataTable

  • Create a new file index.blade.php in the resources/views/ folder.
  • Include the necessary DataTables and jQuery libraries in the <head> section.
  • Create a <table> element with an ID of empTable. This is where our DataTable will be rendered.

jQuery Script –

  • Initialize DataTables on the empTable element, set the required options, and specify the AJAX URL.
  • Within the columns option of the DataTables initialization script, specify the key names that correspond to the data you want to display from the AJAX response.
<!DOCTYPE html>
<html>
<head>
     <title>Datatables AJAX pagination with Search and Sort in Laravel 10</title>

     <!-- Meta -->
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
     <meta charset="utf-8">

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

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

     <!-- Datatable JS -->
     <script src="https://cdn.datatables.net/1.10.25/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('getEmployees')}}",
             columns: [
                 { data: 'id' },
                 { data: 'username' },
                 { data: 'name' },
                 { data: 'email' },
             ]
         });

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

7. Demo

View Demo

8. Conclusion

In this article, we have learned how to implement AJAX pagination with search and sort in Laravel 10 without using external packages. By using the Datatables plugin, we created a dynamic and interactive data table.

We started by creating a database table using migrations. Then, we defined routes and a controller to handle AJAX requests and retrieve data from the database.

Next, we created a view using the Datatables plugin to display the data table. We configured the plugin to fetch data through AJAX requests and specified the columns to be displayed.

Finally, we updated the route definition and included the necessary CSS and JavaScript files to complete the implementation. This functionality allows users to search, sort, and paginate through large datasets without page refreshes.

To make sure­ your Laravel app has the best data display possible­, keep in mind the customization options offe­red by Datatables. The­se tools he­lp you create powerful and attractive­ data tables that are suited pe­rfectly for your project’s require­ments. With Laravel and Datatables, you can cre­ate visually compelling tables with rich functionalitie­s to help enhance use­r experience­.

You can also check out this tutorial to learn how to add custom filters in DataTable.

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

© 2016-2023 Makitweb, All rights reserved