...
Laravel, Tutorials

Laravel datatable using ajax

13 February 2019

Laravel yajra datatable package

We use yajra/laravel-datatables-oracle package for datatables. In this tutorial, we use laravel 5.6, but you can use this example for Laravel 5.1+ version. It gives the functionalities like search, sort, pagination on a table.

It is mainly used when we have millions of records and display all records in view then site will be slow. So we are using datatable using ajax in laravel for fetch find the specific records from table and increase page speed.

Laravel yajra package is created to handle server-side works of DataTables. It jQuery Plugin via AJAX option by using Eloquent ORM, Fluent Query Builder or Collection.

1)Install yajra package using cmd

First, we will install yajra/laravel-datatables-oracle package by writing following command in cmd.

        composer require yajra/laravel-datatables-oracle:"~8.0"

2)Service Provider & Facade

Find the providers in config >> app.php file and register the DatatablesServiceProvider.

'providers' => [
    Yajra\DataTables\DataTablesServiceProvider::class,
]

Place the aliases in config >> app.php file and register the aliases.

'aliases' => [
    'DataTables' => Yajra\DataTables\Facades\DataTables::class,
]

3)Configure yajra datatable package

         php artisan vendor:publish --provider="Yajra\DataTables\DataTablesServiceProvider"

4)JDatatable data from backend

Lets include the required bootstrap, datatables js and css files along with jquery.Define datatable in jquery with table id. In jquery datatable initComplete where i define custom column filteration select dropdown of status column.

<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/fixedheader/3.1.5/js/dataTables.fixedHeader.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.2.3/js/responsive.bootstrap.min.js"></script>
<script>
$.fn.dataTable.ext.errMode = 'none';
var table = $('#users_table').DataTable({
    processing: true,
    serverSide: true,
    responsive: true,
    ajax: "{{ route('get-users') }}",
    columns: [
        {data: 'delete', name: 'delete'},
        {data: 'id', name: 'id'},
        {data: 'name', name: 'name'},
        {data: 'email', name: 'email'},
        {data: 'status', name: 'status'},
        {data: 'action', name: 'action'},
    ],
    columnDefs: [{
            orderable: false,
            className: 'checkbox',
            targets: [0, 'no-sort'],
        }],
    "order": [],
    "info": true,
    "ordering": true,
    "deferRender": true,
    initComplete: function () {
                this.api().columns(4).every(function () {
                    var column = this;
                    var select = $('<label><select id="filter_role" class="form-control input-sm" name="filter_role"><option value="">Status</option></select></label>')
                            .appendTo(".dataTables_length");
                    $("#filter_role").on('change', function () {
                        var category = $("#filter_role").val();
                        var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val());
                        column.search(val ? val : '', true, false)
                                .draw();
                    });
                    column.data().unique().sort().each(function (d, j) {
                        $("#filter_role").append('<option value="' + d + '">' + d + '</option>')
                    });
                });
            },
    select: {
        style: 'os',
        selector: 'td:first-child'
    }
}).draw();
</script>

5)Controller datatable data

In getusers() function fetch data from datatbase and send to the view page.

<?php
use DataTables;
use App\User;
class UsersController extends Controller {
public function index() {
    return view('admin.user.list');
}
public function getusers() {
    $users = User::select(['id', 'name','lname', 'email', 'status'])->with('roles')->where('role_id', '!=', 1)->get();

    return DataTables::of($users)
        ->editColumn('name', function ($user) {
            return $user->name . ' ' . $user->lname;
        })
        ->editColumn('delete', function ($user) {
            return '<div class="custom-control custom-checkbox m-0">
                            <input type="checkbox" onclick="check('.$user->id.')" class="custom-control-input checkbox" id="item' . $user->id . '" name="delete_users[]" value="' . $user->id . '">
                            <label class="custom-control-label" for="item' . $user->id . '"></label>
                        </div>';
        })->addColumn('action', function ($user) {
            $delete = "'" . route('user-delete', ['id' => $user->id]) . "'";
            return '<a href="' . route('user-view', ['id' => $user->id]) . ' " class="info p-0" data-original-title="" title="view">
                            <i class="ft-user font-medium-3 mr-2"></i>
                        </a>
                        <a href="' . route('user-edit', ['id' => $user->id]) . ' " class="success p-0" data-original-title="" title="edit">
                            <i class="ft-edit-2 font-medium-3 mr-2"></i>
                        </a>
                        <a onclick="confirm_delete(' . $delete . ')" class="danger p-0" data-original-title="" title="delete">
                            <i class="ft-x font-medium-3 mr-2"></i>
                        </a>';
        })->rawColumns(['name', 'delete', 'action'])
        ->make(true);
}
?>

Here, what we have done is, when the page is loaded, we send an AJAX request to the server and get the exact data, we need to display on the table. In our case, it is id, name, and email. It appends all the data to the #users_table id with searching, sorting, and pagination functionality.

Leave a Comments