Laravel 6 Datatables Custom filter example tutorial

February 2, 2020 XpertPhp Comments 0 Comment

Today, We will discuss about how to use and custom datatables in laravel 6. We can easily searching, pagination, and ordering the data using yajra datatable.
The yajra data table is an oracle package and it provides facilities like sorting, searching, pagination and ordering. it is given quick response data because it’s used ajax and it’s layout very nice therefore users often use.
Now, We will create the yajra data table and yajra custom filter using the below step in laravel 6.

Overview

Step 1: Install laravel
Step 2: Setting Database Configuration
Step 3: The Database Migration
Step 4: Create Dummy Record Data
Step 5: Install yajra Package
Step 6: Add providers and aliases
Step 7: Create Controller
Step 8: Create Route
Step 9: Create a view file
Step 1: Install laravel

Install the laravel using the below command.

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

1	composer create-project --prefer-dist laravel/laravel laravel6_datatable

Step 2: Setting Database Configuration

After the complete installation of laravel. we have to database configuration. now we will open the .env file and change the database name, username, password in the .env file. See below changes in a .env file.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=Enter_Your_Database_Name(larave6_datatable)
DB_USERNAME=Enter_Your_Database_Username(root)
DB_PASSWORD=Enter_Your_Database_Password(root)

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=Enter_Your_Database_Name(larave6_datatable)

DB_USERNAME=Enter_Your_Database_Username(root)

DB_PASSWORD=Enter_Your_Database_Password(root)

Step 3: The Database Migration

Create The table using the below command.

php artisan migrate

1	php artisan migrate

Step 4: Create Dummy Record Data

Now we will add a dummy record in the ‘users’ table using the laravel tinker command.

php artisan tinker

factory(App\User::class, 100)->create();

php artisan tinker

factory(App\User::class, 100)->create();

Step 5: Install yajra Package

Now, We will install yajra package using below command.

composer require yajra/laravel-datatables-oracle

1	composer require yajra/laravel-datatables-oracle

Step 6: Add providers and aliases

We will add below providers and aliases in the “config/app.php” file.

'providers' => [

	....

	Yajra\Datatables\DatatablesServiceProvider::class,

],

'aliases' => [

	....

	'Datatables' => 'Yajra\Datatables\Facades\Datatables',

]

'providers' => [

....

Yajra\Datatables\DatatablesServiceProvider::class,

'aliases' => [

....

'Datatables' => 'Yajra\Datatables\Facades\Datatables',

]

Step 7: Create Controller

Now, We will create the controller using the below command and paste below code in this controller.

php artisan make:controller DataTableController --resource

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Datatables;
use App\User;

class DataTableController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('datatable.list');
    }


    public function fetch()
    {
        //return Datatables::of(User::query())->make(true);
        
        $start_date = (!empty($_GET["start_date"])) ? ($_GET["start_date"]) : ('');
        $end_date = (!empty($_GET["end_date"])) ? ($_GET["end_date"]) : ('');
        if($start_date && $end_date){
         $start_date = date('Y-m-d', strtotime($start_date));
         $end_date = date('Y-m-d', strtotime($end_date));
        
         User::query()->whereRaw("date(users.created_at) >= '" . $start_date . "' AND date(users.created_at) <= '" . $end_date . "'");
        }
        $query = User::query()->select('*');
        return datatables()->of($query)->make(true);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}

100

101

102

103

104

105

php artisan make:controller DataTableController --resource

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Datatables;

use App\User;

class DataTableController extends Controller

{

/**

* Display a listing of the resource.

* @return \Illuminate\Http\Response

public function index()

{

return view('datatable.list');

}

public function fetch()

{

//return Datatables::of(User::query())->make(true);

$start_date = (!empty($_GET["start_date"])) ? ($_GET["start_date"]) : ('');

$end_date = (!empty($_GET["end_date"])) ? ($_GET["end_date"]) : ('');

if($start_date && $end_date){

$start_date = date('Y-m-d', strtotime($start_date));

$end_date = date('Y-m-d', strtotime($end_date));

User::query()->whereRaw("date(users.created_at) >= '" . $start_date . "' AND date(users.created_at) <= '" . $end_date . "'");

}

$query = User::query()->select('*');

return datatables()->of($query)->make(true);

}

/**

* Show the form for creating a new resource.

* @return \Illuminate\Http\Response

public function create()

{

}

/**

* Store a newly created resource in storage.

* @param \Illuminate\Http\Request $request

* @return \Illuminate\Http\Response

public function store(Request $request)

{

}

/**

* Display the specified resource.

* @param int $id

* @return \Illuminate\Http\Response

public function show($id)

{

}

/**

* Show the form for editing the specified resource.

* @param int $id

* @return \Illuminate\Http\Response

public function edit($id)

{

}

/**

* Update the specified resource in storage.

* @param \Illuminate\Http\Request $request

* @param int $id

* @return \Illuminate\Http\Response

public function update(Request $request, $id)

{

}

/**

* Remove the specified resource from storage.

* @param int $id

* @return \Illuminate\Http\Response

public function destroy($id)

{

}

Step 8: Create Route

Add the below following route code in the “routes/web.php” file.

<?php

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

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



Route::get('datatable', 'DataTableController@index');
Route::get('fetch', 'DataTableController@fetch');
?>

<?php

|--------------------------------------------------------------------------

| Web Routes

|--------------------------------------------------------------------------

| Here is where you can register web routes for your application. These

| routes are loaded by the RouteServiceProvider within a group which

| contains the "web" middleware group. Now create something great!

Route::get('/', function () {

return view('welcome');

});

Route::get('datatable', 'DataTableController@index');

Route::get('fetch', 'DataTableController@fetch');

Step 9: Create a view file.

Finally, We will create list.blade.php file in the “resources/views/datatable/” folder directory and paste below code.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Laravel Datatable using Yajra Tutorial Example</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <link  href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script>
        $(function() {
              $.ajaxSetup({
              headers: {
                 'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
              }
             });
            $('#dataTable').DataTable({
                processing: true,
                serverSide: true,
                ajax: '{{ url('fetch') }}',
                data: function (d) {
                 d.start_date = $('#start_date').val();
                 d.end_date = $('#end_date').val();
                },
                columns: [
                    { data: 'id', name: 'id' },
                    { data: 'name', name: 'name' },
                    { data: 'email', name: 'email' },
                    { data: 'created_at', name: 'created_at' }
                ]
            });
           $('#btnFiterSubmitSearch').click(function(){
             $('#laravel_datatable').DataTable().draw(true);
           });
        });
    </script>
</head>
<body>
 <div class="container">
    <h2>Laravel 6 Datatables Custom Filter Example Tutorial - XpertPhp</h2>
    <div class="form-group col-lg-5">
      <label for="start_date">Start Date:</label>
      <input type="date" name="start_date" id="start_date" class="form-control datepicker-autoclose" placeholder="Please select start date"> 
    </div>
    <div class="form-group col-lg-5">
      <label for="end_date">End Date:</label>
      <input type="date" name="end_date" id="end_date" class="form-control datepicker-autoclose" placeholder="Please select end date"> 
    </div>
    <div class="col-lg-2">
    	<button type="button" id="btnFiterSubmitSearch" class="btn btn-default">Submit</button>
    </div>
    
    <table class="table table-bordered" id="dataTable">
        <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Created</th>
        </tr>
        </thead>
    </table>
</div>
</body>
</html>

<!DOCTYPE html>

<head>

<title>Laravel Datatable using Yajra Tutorial Example</title>

$(function() {

$.ajaxSetup({

headers: {

'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')

}

});

$('#dataTable').DataTable({

processing: true,

serverSide: true,

ajax: '{{ url('fetch') }}',

data: function (d) {

d.start_date = $('#start_date').val();

d.end_date = $('#end_date').val();

columns: [

{ data: 'id', name: 'id' },

{ data: 'name', name: 'name' },

{ data: 'email', name: 'email' },

{ data: 'created_at', name: 'created_at' }

]

});

$('#btnFiterSubmitSearch').click(function(){

$('#laravel_datatable').DataTable().draw(true);

});

</script>

</head>

<body>

<h2>Laravel 6 Datatables Custom Filter Example Tutorial - XpertPhp</h2>

<label for="start_date">Start Date:</label>

</div>

</div>

<button type="button" id="btnFiterSubmitSearch" class="btn btn-default">Submit</button>

</div>

<thead>

<tr>

<th>Email</th>

<th>Created</th>

</tr>

</thead>

</table>

</div>

</body>

</html>

Now, We can run this example using below command.

php artisan serve

1	php artisan serve

Please follow and like us:

Related posts: