laravel-8-advanced-5-jquery-datable-with-server-side-pagination-64183aa03f5ea1679309472.jpg

Laravel 8 Advanced - #5 Jquery Datable with Server Side pagination

Laravel

Subhadip Ghorui

1 year ago

Subhadip Ghorui

In this tutorial we are going learn how to use JQuery Data Tables with Server Side rendering. ClouldTables. DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.

 

It can used with Static HTML table, Ajax and also has built in Server-side api for server side pagination. This is a high popular library for showing datasets or collection in a advanced table format with barely writing any complex code. With the simple one line of code you can create table with dynamic pagination, sortable column and ajax search.

For more information please visit the official site https://datatables.net/. With hundreds of examples and boiler plate code it is the most popular JQuery library in frontend development. In this blog we will focus focus on server side api for Laravel application.

 

1. Server-side API

For server side API we use the incoming request data from data table and make a dynamic Laravel query to fetch data from database. The benefit of not using any library is to get rid of any dependency.

Create a UsersController.php controller.

public function index(){
        return view('users.index');
    	}
  
public function listJson(Request $request){
      
        // Page Length
        $pageNumber = ( $request->start / $request->length )+1;
        $pageLength = $request->length;
        $skip       = ($pageNumber-1) * $pageLength;

        // Page Order
        $orderColumnIndex = $request->order[0]['column'] ?? '0';
        $orderBy = $request->order[0]['dir'] ?? 'desc';


        // Build Query
        // Main
        $query = \DB::table('users')->select('*');

        // Search
        $search = $request->cSearch;
        $query = $query->where(function($query) use ($search){
            $query->orWhere('name', 'like', "%".$search."%");
            $query->orWhere('email', 'like', "%".$search."%");
            $query->orWhere('remember_token', 'like', "%".$search."%");
        });

        $orderByName = 'name';
        switch($orderColumnIndex){
            case '0':
                $orderByName = 'name';
                break;
            case '1':
                $orderByName = 'email';
                break;
            case '2':
                $orderByName = 'remember_token';
                break;
            case '3':
                $orderByName = 'created_at';
                break;
            default:
                $orderByName = 'name';
                break;
        }

        $query = $query->orderBy($orderByName, $orderBy);
        $recordsFiltered = $recordsTotal = $query->count();
        $users = $query->skip($skip)->take($pageLength)->get();

        return response()->json(["draw"=> $request->draw, "recordsTotal"=> $recordsTotal, "recordsFiltered" => $recordsFiltered, 'data' => $users], 200);
    }

 

Create views/users/index.html file

<!doctype html>
<html lang="en">

<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="csrf-token" content="{{ csrf_token() }}" />
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css"
        integrity="sha384-xOolHFLEh07PJGoPkLv1IbcEPTNtaed2xpHsD9ESMhqIYd0nLMwNLD69Npy4HI+N" crossorigin="anonymous">

    <title>Users Table</title>

</head>

<body>
    <div class="container">
        <div class="row">
            <div class="col-12 my-5">
                <h3>Users Lists</h3>
            </div>
            <div class="col-12 form-group">
                <input type="search" name="search" id="search" class="form-control"/>
                <button class="btn btn-primary my-3" id="searchBtn">Search</button>
            </div>
            <div class="col-12">
                <table id="datatable" class="table" style="width:100%">
                    <thead class="table-dark">
                        <tr>
                            <td>Name</td>
                            <td>Email</td>
                            <td>Remember Token</td>
                            <td>Created At</td>
                            <td></td>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>

    <!-- Option 1: jQuery and Bootstrap Bundle (includes Popper) -->
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"
        integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous">
    </script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"
        integrity="sha384-Fy6S3B9q64WdZWQUiU+q4/2Lc9npb8tCaSX9FK7E8HnRr0Jz8D6OP9dO5Vg3Q9ct" crossorigin="anonymous">
    </script>

    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/dataTables.bootstrap4.min.js"></script>

    {{-- Data table Code --}}
    <script>
        $.ajaxSetup({
            headers: {
                'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
            }
        });
        $(document).ready(function() {
            $('#datatable').DataTable({
                processing: true,
                serverSide: true,
                ajax: {
                    url: '{{ route('users.listJson') }}',
                    type: "POST",
                    data: function(data) {
                        data.cSearch = $("#search").val();
                    }
                },
                order: ['1', 'DESC'],
                pageLength: 10,
                searching: false,
                aoColumns: [
                    {
                        data: 'name',
                    },
                    {
                        data: 'email',
                    },
                    {
                        data: 'remember_token',
                    },
                    {
                        data: 'created_at',
                        width: "20%",
                    },
                    {
                        data: 'id',
                        width: "20%",
                        render: function(data, type, row) {
                            return `<a href="${row.id}">View</a>`;
                        }
                    }
                ]
            });
        });

        $("#searchBtn").click(function(){
            $('#datatable').DataTable().ajax.reload();
        })
    </script>
</body>

</html>

 

In server side post ajax request don’t forget to attach the X-CSRF token to ajax header. Under the hood datable is using the ajax request for sending http call to the server.

 

Visit GitHub Repository for Full series: https://github.com/subhadipghorui/laravel-advanced-topics

View Full Playlist – Laravel Advanced Topic: https://github.com/subhadipghorui/laravel-advanced-topics

 

Thank You. Please share this if it is helpful to you.

0 people like this
1604 views
0 comments
Share it on your social media account.

Please Sign in to post comments - Sing in or Register

0 Comments