Datatables AJAX pagination with Search and Sort in Laravel 8

   

DataTables AJAX pagination with Search and Sort in Laravel 8


With pagination, it is easier to display a huge list of data on the page.

You can create pagination with and without AJAX.

There are many jQuery plugins are available for adding pagination. One of them is DataTables.

In this tutorial, I show how you can add Datatables AJAX pagination without the Laravel package in Laravel 8.

Demo

Contents

  1. Database Configuration
  2. Create Table
  3. Download
  4. Model
  5. Route
  6. Controller
  7. View
  8. Demo
  9. Conclusion

1. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

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

3. Download

  • 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.
  • 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>

4. Model

  • Create Employees Model.
php artisan make:model Employees
  • Open app/Models/Employees.php file.
  • Specify mass assignable Model attributes – username, name, and email using the $filliable property.

Completed Code

<?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'
    ];
}

5. Route

  • 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');

6. Controller

  • Create EmployeesController Controller.
php artisan make:controller EmployeesController
  • Open app/Http/Controllers/EmployeesController.php file.
  • Import Employees Model.

Create 2 methods –

  • index() – Load index view.
  • getEmployees() – This method is to handle DataTables AJAX request.

Read DataTables values and assign them to the variables.

Count total records with and without search filter from 'employees' table and assign to $totalRecords and $totalRecordswithFilter variable.

Fetch records from 'employees' table and assign to $records variable.

Loop on the fetched data and initialize $data Array with keys that are specified in the 'columns' option while initializing DataTables.

Initialize $response Array with required values.

Return $response Array in JSON format.

Completed Code

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

7. View

Create index.blade.php file in resources/views/ folder.

Include DataTables and jQuery library.

Create <table id='empTable' >.

jQuery Script –

  • Initialize DataTables on #empTable.
  • Set processing: true, serverSide: true.
  • Set AJAX URL to {{route('getEmployees')}}.
  • With 'columns' option specifies key names that need to be read from the AJAX response.

Completed Code

<!DOCTYPE html>
<html>
<head>
     <title>Datatables AJAX pagination with Search and Sort in Laravel 8</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>

8. Demo

View Demo

9. Conclusion

Make sure to return the DataTables response in a defined format otherwise, data not load.

If you caught any error while adding DataTables then use the browser network tab to debug by viewing the AJAX request response.

« Newest
Newer Posts
Oldest »
Older Posts

Comments