article

Saturday, January 7, 2023

Laravel 9 Ajax Datatables Filter

Laravel 9 Ajax Datatables Filter

Download Laravel App

composer create-project --prefer-dist laravel/laravel my-app
C:\xampp\htdocs\laravel>composer create-project --prefer-dist laravel/laravel my-app

Connecting our Database

open .env file root directory.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laraveldb
DB_USERNAME=root
DB_PASSWORD=

Create Controller
php artisan make:controller StudentController
C:\xampp\htdocs\laravel\my-app>php artisan make:controller StudentController
app\Http\Controllers\StudentController.php
//app\Http\Controllers\StudentController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller
{
    public function index()
    {
        $standards = Student::select('standard')
            ->groupBy('standard')
            ->get();

        $results = Student::select('result')
            ->groupBy('result')
            ->get();

        return view('students', compact('standards', 'results'));
    }

    public function getStandard(Request $request)
    {
        if ($request->ajax()) {
            $standards = Student::select('standard')
                ->groupBy('standard')
                ->get();

            return response()->json($standards);
        }
    }

    public function getResult(Request $request)
    {
        if ($request->ajax()) {

            $results = Student::select('result')
                ->groupBy('result')
                ->get();

            return response()->json($results);
        }
    }

    public function records(Request $request)
    {
        if ($request->ajax()) {

            if (request('std') && request('res')) {
                $students = Student::where('standard', '=', request('std'))->where('result', '=', request('res'))
                    ->latest()
                    ->get();
            } else {
                $students = Student::when(request('std'), function ($query) {
                    $query->where('standard', '=', request('std'));
                })
                    ->when(request('res'), function ($query) {
                        $query->where('result', '=', request('res'));
                    })
                    ->latest()
                    ->get();
            }

            return response()->json([
                'students' => $students
            ]);
        } else {
            abort(403);
        }
    }
}
Database Migration
php artisan make:model Student -m
C:\xampp\htdocs\laravel\my-app>php artisan make:model Student -m
database/migrations/create_students_table.php
//atabase/migrations/create_students_table.php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integer('standard');
            $table->integer('percentage');
            $table->string('result');
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::dropIfExists('students');
    }
};
run this migration
C:\xampp\htdocs\laravel\my-app>php artisan migrate

database table data
CREATE TABLE `students` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `standard` int(11) NOT NULL,
  `percentage` int(11) NOT NULL,
  `result` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`, `standard`, `percentage`, `result`, `created_at`, `updated_at`) VALUES
(1, 'Cairocoders Ednalan', 5, 75, '75', '2022-12-22 08:38:25', '2022-12-22 08:38:25'),
(2, 'Charde Marshall', 8, 75, '75', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(3, 'Airi Satou', 7, 85, '85', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(4, 'Angelica Ramos', 5, 95, '95', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(5, 'Ashton Cox', 6, 95, '95', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(6, 'Bradley Greer', 7, 80, '80', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(7, 'Brenden Wagner', 8, 80, '82', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(8, 'Brielle Williamson', 9, 85, '85', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(9, 'Bruno Nash', 10, 80, '86', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(10, 'Caesar Vance', 7, 90, '90', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(11, 'Cara Stevens', 8, 90, '91', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),
(12, 'Cedric Kelly', 2, 90, '92', '2022-12-22 00:38:25', '2022-12-22 00:38:25');
  
View Blade
Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

Jquery
https://jquery.com/download/
CDN : jsDelivr CDN
https://www.jsdelivr.com/package/npm/jquery
https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

https://datatables.net/
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.
resources/views/students.blade.php
//resources/views/students.blade.php
<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Laravel</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css" />
</head>
<body>
    <div class="container">
        <div class="row">
            <h1 class="text-center">Laravel 9 Ajax Datatables Filter</h1>
            <hr>
        </div>
        <div class="row">
            <div class="col-12">
                <div class="row">
                    <div class="col-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <label class="input-group-text" for="inputGroupSelect01">Standard</label>
                            </div>
                            <select class="custom-select form-select" id="select_std">
                                <option value="">Choose...</option>
                            </select>
                        </div>
                    </div>
                    <div class="col-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <label class="input-group-text" for="inputGroupSelect01">Result</label>
                            </div>
                            <select class="custom-select form-select" id="select_res">
                                <option value="">Choose...</option>
                            </select>
                        </div>
                    </div>
                </div>
                <div>
                    <button id="filter" class="btn btn-sm btn-outline-success">Filter</button>
                    <button id="reset_std" class="btn btn-sm btn-outline-success">Reset Standard</button>
                    <button id="reset_res" class="btn btn-sm btn-outline-success">Reset Result</button>
                    <button id="reset" class="btn btn-sm btn-outline-warning">Reset</button>
                </div>
            </div>
        </div>
        <div class="row">
                    <div class="col-12">
                        <div class="table-responsive">
                            <table class="display" id="record_table" style="width:100%">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Name</th>
                                        <th>Standard</th>
                                        <th>Percentage</th>
                                        <th>Result</th>
                                        <th>Date</th>
                                    </tr>
                                </thead>
                            </table>
                        </div>
                    </div>
        </div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment.min.js"></script>
<script>
        function fetch_std() {
            $.ajax({
                url: "{{ route('standards') }}",
                type: "GET",
                dataType: "json",
                success: function(data) {
                    var stdBody = "";
                    for (var key in data) {
                        stdBody +=
                            `<option value="${data[key]['standard']}">${data[key]['standard']}</option>`;
                    }
                    $("#select_std").append(stdBody);
                }
            });
        }
        fetch_std();
        // Fetch Result
        function fetch_res() {
            $.ajax({
                url: "{{ route('results') }}",
                type: "GET",
                dataType: "json",
                success: function(data) {
                    var resBody = "";
                    for (var key in data) {
                        resBody += `<option value="${data[key]['result']}">${data[key]['result']}</option>`;
                    }
                    $("#select_res").append(resBody);
                }
            });
        }
        fetch_res();
        // Fetch Records
        function fetch(std, res) {
            $.ajax({
                url: "{{ route('students/records') }}",
                type: "GEt",
                data: {
                    std: std,
                    res: res
                },
                dataType: "json",
                success: function(data) {
                    var i = 1;
                    $('#record_table').DataTable({
                        "data": data.students,
                        "responsive": true,
                        "columns": [{
                                "data": "id",
                                "render": function(data, type, row, meta) {
                                    return i++;
                                }
                            },
                            {
                                "data": "name"
                            },
                            {
                                "data": "standard",
                                "render": function(data, type, row, meta) {
                                    return `${row.standard}th Standard`;
                                }
                            },
                            {
                                "data": "percentage",
                                "render": function(data, type, row, meta) {
                                    return `${row.percentage}%`;
                                }
                            },
                            {
                                "data": "result"
                            },
                            {
                                "data": "created_at",
                                "render": function(data, type, row, meta) {
                                    return moment(row.created_at).format('DD-MM-YYYY');
                                }
                            }
                        ]
                    });
                }
            });
        }
        fetch();
        // Filter
        $(document).on("click", "#filter", function(e) {
            e.preventDefault();
            var std = $("#select_std").val();
            var res = $("#select_res").val();
            if (std !== "" && res !== "") {
                $('#record_table').DataTable().destroy();
                fetch(std, res);
            } else if (std !== "" && res == "") {
                $('#record_table').DataTable().destroy();
                fetch(std, '');
            } else if (std == "" && res !== "") {
                $('#record_table').DataTable().destroy();
                fetch('', res);
            } else {
                $('#record_table').DataTable().destroy();
                fetch();
            }
        });
        // Reset Standard
        $(document).on("click", "#reset_std", function(e) {
            e.preventDefault();
            $("#select_std").html(`<option value="">Choose...</option>`);
            var res = $("#select_res").val();
            if (res == "") {
                $('#record_table').DataTable().destroy();
                fetch();
                fetch_std();
            } else {
                $('#record_table').DataTable().destroy();
                fetch('', res);
                fetch_std();
            }
        });
        // Reset Result
        $(document).on("click", "#reset_res", function(e) {
            e.preventDefault();
            $("#select_res").html(`<option value="">Choose...</option>`);
            var std = $("#select_std").val();
            if (std == "") {
                $('#record_table').DataTable().destroy();
                fetch();
                fetch_res();
            } else {
                $('#record_table').DataTable().destroy();
                fetch(std, '');
                fetch_res();
            }
        });
        // Reset
        $(document).on("click", "#reset", function(e) {
            e.preventDefault();
            $("#select_std").html(`<option value="">Choose...</option>`);
            $("#select_res").html(`<option value="">Choose...</option>`);
            $('#record_table').DataTable().destroy();
            fetch();
            fetch_std();
            fetch_res();
        });
    </script>
</body>
</html>
Routes
routes/web.php
//routes/web.php
<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ImageController;
use App\Http\Controllers\StudentController;

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

Route::get('students', [StudentController::class, 'index'])->name('students');
Route::get('standards', [StudentController::class, 'getStandard'])->name('standards');
Route::get('results', [StudentController::class, 'getResult'])->name('results');
Route::get('students/records', [StudentController::class, 'records'])->name('students/records');
Run C:\xampp\htdocs\laravel\my-app>php artisan serve
Starting Laravel development server: http://127.0.0.1:8000

Related Post