Custom filter in DataTable AJAX pagination in Laravel 9

DataTable by default has a single search box that uses for searching globally.

If default search filter is not fulfilling your requirement then you can customize and add you own filter elements like – dropdown, date filter, textbox, etc.

In this tutorial, I show how you can add custom filter in DataTable AJAX pagination in Laravel 9.

How to add Custom filter in DataTable AJAX pagination in Laravel

Demo

Database Configuration
Table structure
Download
Model
Controller
Route
View
Demo
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. Table structure

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',60);
       $table->string('name');
       $table->string('email',80);
       $table->string('city',80);
       $table->string('gender',10);
       $table->timestamps();
    });
}

Run the migration –

php artisan migrate

The table is been created and I added some records to 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, email, gender, and city using the $fillable 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','gender','city'
   ];
}

5. Controller

Create EmployeesController controller.

php artisan make:controller EmployeesController

Import Employees model.

Create 2 methods –

index() – Fetch distinct city records from employees table and assign to $data['cities']. Load index view and pass $data.
getEmployees() – Using this method return DataTable records.

Read DataTable values and assign them to the variables.

Read custom filter values (searchCity, searchGender, and searchName) and assign them to the variables.

Fetch total records with and without filters from employees table. While counting records add custom filter in where clause if variable value is not empty.

Fetch records from employees table where specify custom filter values in where clause if variable is not empty.

Loop on the fetched records and assign values to $data_arr Array.

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\Models\Employees;

class EmployeesController extends Controller {

   public function index(){

      $data['cities'] = Employees::distinct()->get(['city']);

      return view('index',$data);
   }

   // Fetch DataTable data
   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

      // Custom search filter 
      $searchCity = $request->get('searchCity');
      $searchGender = $request->get('searchGender');
      $searchName = $request->get('searchName');

      // Total records
      $records = Employees::select('count(*) as allcount');

      ## Add custom filter conditions
      if(!empty($searchCity)){
          $records->where('city',$searchCity);
      }
      if(!empty($searchGender)){
          $records->where('gender',$searchGender);
      }
      if(!empty($searchName)){
          $records->where('name','like','%'.$searchName.'%');
      }
      $totalRecords = $records->count();

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

      ## Add custom filter conditions
      if(!empty($searchCity)){
         $records->where('city',$searchCity);
      }
      if(!empty($searchGender)){
         $records->where('gender',$searchGender);
      }
      if(!empty($searchName)){
         $records->where('name','like','%'.$searchName.'%');
      }
      $totalRecordswithFilter = $records->count();

      // Fetch records
      $records = Employees::orderBy($columnName,$columnSortOrder)
                 ->select('users_4.*')
                 ->where('users_4.name', 'like', '%' .$searchValue . '%');
      ## Add custom filter conditions
      if(!empty($searchCity)){
         $records->where('city',$searchCity);
      }
      if(!empty($searchGender)){
         $records->where('gender',$searchGender);
      }
      if(!empty($searchName)){
         $records->where('name','like','%'.$searchName.'%');
      }
      $employees = $records->skip($start)
                   ->take($rowperpage)
                   ->get();

      $data_arr = array();
      foreach($employees as $employee){

         $username = $employee->username;
         $name = $employee->name;
         $email = $employee->email;
         $gender = $employee->gender;
         $city = $employee->city;

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

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

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

}

6. Route

Open routes/web.php file.
Define 2 routes –
- / – Load index view.
- /getEmployees – This is GET type route for AJAX request to load datatable 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');

7. View

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

Include DataTables and jQuery library in <head > section.

HTML –

Custom filter

I created 3 filtering elements –

- 1st element is a dropdown element that stores cities list.
- 2nd element is a dropdown element that store gender names.
- 3rd element is a textbox element to filter records by name.
DataTable

Create <table id="empTable" > to initialize Datatables.

Script –

Initialize DataTable on #empTable and assign instance to empTable. Send AJAX request to route('getEmployees').

Pass custom filter values

Use data option to send custom filter values – data.searchCity, data.searchGender, and data.searchName.

In the columns options specify the key names that get read on a successful callback.

Define change event on #sel_city,#sel_gender and keyup event on #searchName.

When event triggers call draw() on DataTable instance – empTable.draw();. Using this redraw DataTable.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <title>How to add Custom filter in DataTable AJAX pagination in Laravel 9</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>

   <!-- Search filter -->
   <div>
       <!-- City -->
       <select id='sel_city'>
          <option value=''>-- Select city --</option>

          @foreach($cities as $city){
             <option value='{{ $city->city }}'>{{ $city->city }}</option>
          @endforeach
       </select>

       <!-- Gender -->
       <select id='sel_gender'>
          <option value=''>-- Select Gender --</option>
          <option value='male'>Male</option>
          <option value='female'>Female</option>
       </select>

       <!-- Name -->
       <input type="text" id="searchName" placeholder="Search Name">
   </div>

   <table id='empTable' width='100%' border="1" style='border-collapse: collapse;'>
      <thead>
         <tr>
            <th>Username</th>
            <th>Name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>City</th>
         </tr>
      </thead>
   </table>

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

      // DataTable
      var empTable = $('#empTable').DataTable({
          processing: true,
          serverSide: true,
          ajax: {
             url:"{{route('getEmployees')}}",
             data: function(data){
                data.searchCity = $('#sel_city').val();
                data.searchGender = $('#sel_gender').val();
                data.searchName = $('#searchName').val();
             }
          },
          columns: [
             { data: 'username' },
             { data: 'name' },
             { data: 'email' },
             { data: 'gender' },
             { data: 'city' },
          ]
      });

      $('#sel_city,#sel_gender').change(function(){
         empTable.draw();
      });

      $('#searchName').keyup(function(){
         empTable.draw();
      });

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

8. Demo

View Demo

9. Conclusion

You need to call draw() on DataTable instance when custom filter event is triggered to fetch filtered records.

You can use the same code if your project is on Laravel 8.

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

How to add Custom filter in DataTable AJAX pagination in Laravel 9

Contents

1. Database Configuration

2. Table structure

3. Download

4. Model

5. Controller

6. Route

7. View

8. Demo

9. Conclusion

Leave a Comment Cancel reply