Laravel 11 Datatables Pagination with AJAX Search and Sort

by

In this Laravel 11 Datatables Pagination Example, you will learn how to use server-side data table Ajax pagination with search and sort in Laravel 11.

Datatable is the most commonly used application for pagination with search and sort functionality. So. here we have implemented easy and best way the Ajax data-tables pagination in Laravel 11.

Datatables AJAX pagination with Search and Sort Laravel 11

Many times new beginners have Yajra Datatables pagination not working issue but if you follow the following steps to implement Laravel Datatable Ajax pagination with search and sort functionality:

#1 Install Laravel App

First, you need to install a fresh laravel application using the following command or you can use your existing application as well.

composer create-project --prefer-dist laravel/laravel laravel-datatables

Next, go inside your app directory:

cd laravel-datatables

#2 Setup Database Credentials

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db name
DB_USERNAME=db user name
DB_PASSWORD=db password

#3 Make Routes

In this step, Navigate to the routes folder and open web.php. Then add the following routes into web.php file:

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserController;

Route::get('users', [ UserController::class, 'index' ]);
Route::post('users/getusers/', [ UserController::class, 'getUser' ])->name('users.getusers');


Route::get('/', function () {
    return view('welcome');
});

#4 Create Controller and Update Logic

Next, you need to create a new controller file. So open the terminal and generate a new controller file using the following command:

php artisan make:controller UserController

After getting the new controller, open the app/Http/Controllers/UserController.php file and update the below code on it.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\User;

class Usercontroller extends Controller
{
    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        return view('users');
    }

    public function getUser(Request $request)
    {
        $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 = User::select('count(*) as allcount')->count();
        $totalRecordswithFilter = User::select('count(*) as allcount')->where('name', 'like', '%' .$searchValue . '%')->count();

        // Fetch records
        $records = User::orderBy($columnName,$columnSortOrder)
            ->where('users.name', 'like', '%' .$searchValue . '%')
            ->select('users.*')
            ->skip($start)
            ->take($rowperpage)
            ->get();

        $data_arr = array();
        $sno = $start+1;
        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;
    }

}

#5 Create Blade File

Next, navigate the resources/views directory and create a users.blade.php blade file. Now open the resources/views/users.blade.php file and put the below code on it.

<!DOCTYPE html>
<html>
<head>
    <!-- Datatables CSS CDN -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha256-aAr2Zpq8MZ+YA/D6JtRD3xtrwpEz2IqOS+pWD/7XKIw=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha256-OFRAJNoaD8L3Br5lglV7VyLRf0itmoBzWUoM+Sji4/8=" crossorigin="anonymous"></script>
    <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.5.1/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>
    <div class="container">
        <div class="row justify-content-center">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header bg-info text-white">Laravel Datatables Pagination with Search and Sort</div>
                    <div class="card-body">
                        @if (session('status'))
                            <div class="alert alert-success" role="alert">
                                {{ session('status') }}
                            </div>
                        @endif
                        <table id='empTable' width='100%' border="1" style='border-collapse: collapse;'>
                            <thead>
                                <tr>
                                    <td>S.no</td>
                                    <td>Name</td>
                                    <td>Email</td>
                                </tr>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script type="text/javascript">
        $(document).ready(function(){

          // DataTable
          $('#empTable').DataTable({
             processing: true,
             serverSide: true,
             ajax: "{{route('users.getusers')}}",
             columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'email' },
             ]
          });
        });
    </script>
</body>
</html>

#6 Run Migration and Add Test Data

Now open your terminal and run the migrate command:

php artisan migrate

You can see in your database some tables are generated including the users table;

So now we need some dummy users for testing purposes, For fake records, we will use the Laravel faker here. you just need to run the below command on your terminal:

php artisan tinker
    
User::factory()->count(20)->create()

Learn Also: Laravel Seeder to Insert Multiple Records in Database

#7 Run Your Application

Let’s run your application using the following command:

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/users

Learn also: Laravel Ajax CRUD Example Tutorial

Conclusion

I hope this example helps you a lot. These Laravel versions are supported for Laravel Datatables Pagination with AJAX Search and Sort example:

  • Laravel 8
  • Laravel 9
  • Laravel 10
  • Laravel 11

Leave a Comment