Laravel Datatables Pagination with Search and Sort Example

by

Laravel Datatables Pagination with Search and Sort Example; In this tutorial you will learn how to use datatable ajax pagination with search and sort in laravel application.

Datatable provide searching, sorting and pagination using datatable ajax laravel application. This tutorial focuses on Laravel 9, you can use the laravel livewire image upload functionality with laravel 6 and laravel 7.

Laravel 9 DataTable Ajax Pagination with Search And Sort

Follow the following steps to implement laravel Datatable ajax pagination with search and sort functionality:

  • Step 1: Install Laravel App
  • Step 2: Connect App to Database
  • Step 3: Make Routes
  • Step 4: Install Livewire Package
  • Step 5: Build Livewire Component
  • Step 6: Make Routes
  • Step 7: Create Blade File
  • Step 8: Run Development Server

Install Laravel App

First create new laravel 9 application adding the following command in terminal.

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

Now Go into the app:

laravel-datatables

Connect App to Database

Open the .env file and add your database credentials such as database name, username equally important password:

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

Make Routes

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

Route::get('users','UserController@index');
Route::get('/users/getusers/','UserController@getUser')->name('users.getusers');

Create Controller

Now you need to create a controller just adding the below command and generate a new controller UserController file.

php artisan make:controller UserController

After running this command you will find new file in this path “app/Http/Controllers/UserController.php”. Where you saw here already created methods same like below. Now you need put below codes on your functions.

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

}

Create Blade File

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

<!DOCTYPE html>
<html>
<head>
    <title>laravel datatables pagination with search and sort - nicesnippets.com</title>
    <!-- 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 - NiceSnippets.com</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>

Run Laravel Application

 Lets run Laravel Datatables Pagination with Search and Sort Example app using the following command:

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/user

Hope this tutorial help you!

1 thought on “Laravel Datatables Pagination with Search and Sort Example”

Leave a Comment