How to do Serverside Listing and Filtering using JQuery Datatables in Laravel

The Jquery Datatables is a very handy utility tool to perform Serverside Listing, Pagination, Search, and Ordering. So in this post, you’ll learn How to use JQuery Datatables in Laravel.

For this, I’ll take an example of posts that have to be listed and filtered by date range. The First thing is to create a Controller and name it as DatatableController. This controller has two methods on is the index() method to show the blade view and to retrieve JSON data there is the method posts().
In app\Http\Controllers\DatatableController.php

<?php
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\PostModel;
use Illuminate\Http\Request;
use Symfony\Component\Console\Input\Input;

class DatatableController extends Controller{

    public function index( Request $request ){
        $info = [];
        return view( 'datatables.list', $info );
    }

    public function posts( Request $request ){

        $info = [
            'draw' => $request->draw,
            'data' => [],
            'total' => 0,
        ];

        $search = $request->input('search.value');

        $posts = PostModel::orWhere( function($query) use ( $search ) {
            $query->where( "name", "LIKE", "%".$search."%" )->where( "slug", "LIKE", "%".$search."%" );
        } )->dateFilter( $request->from_date, $request->to_date )->take( $request->length )->skip( $request->start )->get();

        $info['total'] = PostModel::orWhere( function($query) use ( $search ) {
            $query->where( "name", "LIKE", "%".$search."%" )->where( "slug", "LIKE", "%".$search."%" );
        } )->dateFilter( $request->from_date, $request->to_date )->count();

        $sl_no_counter = ( $request->start == 0 )? 1 : $request->start+1;

        foreach( $posts as $post ){
            $post->sl_no = $sl_no_counter;
            $sl_no_counter++;
        }

        $info['data'] = $posts;

        return $info;
    }
}

The Datatable excepts and JSON response formatted like shown below.

{
    'data' : [
        // list of items
    ],
    'draw' : '', //sent by datatable itself to server
    'total': '' //The total number of row exists in the table this is for pagination
}

Now for the filtering list, I’ll be scoping queries in Laravel PostModel and filter only those which are under the date range.

public function scopeDateFilter( $query, $from_date=null, $to_date=null ){

    if( !empty( $from_date ) ){
        $from_date = date('Y-m-d 00:00:01', strtotime( $from_date ) );

        $to_date = ( !empty( $to_date ) )? date('Y-m-d 23:59:59', strtotime( $to_date ) ) : date('Y-m-d 23:59:59' );

        $query->whereBetween( 'created_at', [ $from_date, $to_date ] );
    }

    return $query;
}

Complete PostModel Code

In app\Models\PostModel.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class PostModel extends Model
{
    protected $table = 'posts'; 
    protected $primaryKey = 'post_id'; 
    protected $fillable = ['name', 'slug', 'description' ];
    protected $dates = ['deleted_at']; 
    
    // dateFilter
    public function scopeDateFilter( $query, $from_date=null, $to_date=null ){

        if( !empty( $from_date ) ){
            $from_date = date('Y-m-d 00:00:01', strtotime( $from_date ) );

            $to_date = ( !empty( $to_date ) )? date('Y-m-d 23:59:59', strtotime( $to_date ) ) : date('Y-m-d 23:59:59' );

            $query->whereBetween( 'created_at', [ $from_date, $to_date ] );
        }

        return $query;
    }
}
    

Complete JQuery Snippet

In resources\views\datatables\list.blade.php

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Datatables</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

    <link rel="stylesheet" href="http://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css">

    <script src="http://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
</head>
<body>

    <div class="container">
        <h2 class="text-center">Laravel Datatables</h2>
        <div class="table-responsive">
            <hr>
            <div class="row">
                <div class="col-md-3">
                    <label for="">From Date</label>
                    <input type="date" id="from_date" value="" >
                </div>

                <div class="col-md-3">
                    <label for="">To Date</label>
                    <input type="date" id="to_date" value="" >
                </div>

                <div class="col-md-3">
                    <button type="button" class="btn btn-info" onclick="reload_table()" >Filter</button>
                </div>
            </div>
            <hr>

            <table id="posts-table" class="table table-bordered">
                <thead>

                </thead>
                <tbody>

                </tbody>
            </table>
        </div>
    </div>

    <script>
        $(function() {
            var drawer_count = 1;

            $('#posts-table').DataTable({
                "oLanguage": {
                    "sProcessing": '<span>Please wait ...</span>'
                },
                "pagingType": "simple_numbers",
                "paging": true,
                "lengthMenu": [
                    [10, 25, 50],
                    [10, 25, 50]
                ],
                "processing": true,
                "serverSide": true,
                "ordering": false,
                "ajax": {
                    "type": "GET",
                    "url": "{{ url('datatables/posts') }}",
                    "data": function(d) {
                        d.from_date = document.getElementById('from_date').value;
                        d.to_date = document.getElementById('to_date').value;
                    },
                    "dataFilter": function(data) {
                        drawer_count++;
                        var json = jQuery.parseJSON(data);
                        json.draw = json.draw;
                        json.recordsTotal = json.total;
                        json.recordsFiltered = json.total;
                        json.data = json.data;

                        $('#list_table_processing').css('display', 'none');
                        return JSON.stringify(json); // return JSON string
                    }
                },
                "columns": [
                    {"title": "#", "data": "sl_no", "name": "sl_no", "visible": true, "searchable": true},
                    {"title": "Title", "data": "name", "name": "name", "visible": true, "searchable": true},
                    {"title": "Slug", "data": "slug", "name": "slug", "visible": true, "searchable": true},
                    {"title": "Description", "data": "description", "name": "description", "visible": true, "searchable": true},
                ],
            });
        });

        function reload_table() {
            $('#posts-table').DataTable().ajax.reload();
        }
    </script>

</body>
</html>

Output

Ajax Fetching List of items using JQuery Datatables in Laravel

Video Tutorial

Conclusion

We have reached the end of our post on How to do Serverside Listing and Filtering using JQuery Datatables in Laravel. Support us by sharing this post.

Related Posts

Summary
Review Date
Reviewed Item
How to do Serverside Listing and Filtering using JQuery Datatables in Laravel
Author Rating
51star1star1star1star1star
Software Name
Laravel Framework
Software Name
Windows Os, Mac Os, Ubuntu Os
Software Category
Web Development