Laravel Custom Datatables filter and Search

In this tutorial you will learn about the Laravel Custom Datatables filter and Search and its application with practical example.

In this tutorial, I’ll show you how to add custom search or data filter to datatables. In this tutorial, we will be using yajra datatable package for listing of records with pagination, sorting and filter (search) feature. Laravel Yajra datatables package comes with many built-in features for searching and sorting functionality. In this article you will learn to add custom field for searching data without reloading or refreshing of page.

Table Of Contents−

Laravel Custom Datatables filter and Search
Install Laravel 5.8

.env file
Generate Application Key
Set Default String Length

Create Database Table
Run Migration
Create Model

Create Controller
Create Blade / View Files

Create Routes

Before starting with example I assume that you already have fresh laravel 5.8 installation ready, if you have not installed it yet you can follow laravel 5 installation instruction here.

Install Laravel 5.8

First of all we need to create a fresh laravel project, download and install Laravel 5.8 using the below command

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

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

Configure Database In .env file

Now, lets create a MySQL database and connect it with laravel application. After creating database we need to set database credential in application’s .env file.

.env

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

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=larablog

DB_USERNAME=root

DB_PASSWORD=

Generate Application Key

Open terminal and switch to the project directory and run the following command to generate application key and configure cache.

php artisan key:generate

1	php artisan key:generate

php artisan config:cache

1	php artisan config:cache

Set Default String Length

Locate the file “app/Providers/AppServiceProvider”, and add following line of code to the top of the file

use Illuminate\Support\Facades\Schema;

1	use Illuminate\Support\Facades\Schema;

and inside the boot method set a default string length as given below –

Schema::defaultStringLength(191);

1	Schema::defaultStringLength(191);

So this is how “app/Providers/AppServiceProvider” file looks like –

<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
 public function boot() {
 //
 Schema::defaultStringLength(191);
}
 public function register()
 {
 //
 }
}

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;

use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider

{

public function boot() {

Schema::defaultStringLength(191);

}

public function register()

{

}

Create Database Table

Now, we have to define table schema for posts table. Open terminal and let’s run the following command to generate a migration file to create posts table in our database.

php artisan make:migration create_posts_table --create=posts

1	php artisan make:migration create_posts_table --create=posts

Once this command is executed you will find a migration file created under “database/migrations”. lets open migration file and put following code in it –

<?php

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

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('body');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

<?php

use Illuminate\Support\Facades\Schema;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration

{

/**

* Run the migrations.

* @return void

public function up()

{

Schema::create('posts', function (Blueprint $table) {

$table->bigIncrements('id');

$table->string('title');

$table->string('body');

$table->timestamps();

});

}

/**

* Reverse the migrations.

* @return void

public function down()

{

Schema::dropIfExists('posts');

}

Run Migration

Now, run following command to migrate database schema.

php artisan migrate

1	php artisan migrate

After, the migration executed successfully the posts table will be created in database along with migrations, password_resets and users table.

Create Model

Next, we need to create a model called Post using below command.

php artisan make:model Post

1	php artisan make:model Post

Once, the above command is executed it will create a model file Post.php in app directory. Next, we have to assign fillable fields using fillable property inside Post.php file. Open app/Post.php file and put the following code in it –

app/Post.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    //
    protected $fillable = [
        'title', 'body'
    ];
}

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model

{

protected $fillable = [

'title', 'body'

];

}

Install Yajra Datatable Package

In this step, we will install Yajra Datatables Package via the composer dependency manager. Use the following command to install Yajra Datatables Package.

composer require yajra/laravel-datatables-oracle

1	composer require yajra/laravel-datatables-oracle

After Installing Yajra Datatables package, we need to add service provider and alias in config/app.php file as following.

config/app.php

'providers' => [
      // Other service providers…
  Yajra\DataTables\DataTablesServiceProvider::class,
 ],

'aliases' => [
 // Other aliases…
 'Datatables' => Yajra\Datatables\Facades\Datatables::class,
 ],

'providers' => [

// Other service providers…

Yajra\DataTables\DataTablesServiceProvider::class,

'aliases' => [

// Other aliases…

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

Create Controller

Next, we have to create a controller to handle Ajax CRUD Operations. Create a controller named AjaxCrudController using command given below –

php artisan make:controller dtable/AjaxCrudController

1	php artisan make:controller dtable/AjaxCrudController

Once the above command executed, it will create a controller file AjaxCrudController.php in app/Http/Controllers/dtable directory. Open the dtable/AjaxCrudController.php file and put the following code in it.

app/Http/Controllers/dtable/AjaxCrudController.php

<?php

namespace App\Http\Controllers\dtable;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Post;
use Redirect,Response;

class AjaxCrudController extends Controller
{

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
         return view('dtable.custom_filter');
    }

    public function get_custom_posts(){
        $postsQuery = Post::query();

        $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));
         
         $postsQuery->whereRaw("date(posts.created_at) >= '" . $start_date . "' AND date(posts.created_at) <= '" . $end_date . "'");
        }
        $posts = $postsQuery->select('*');
        return datatables()->of($posts)
            ->make(true);
    }

}

<?php

namespace App\Http\Controllers\dtable;

use Illuminate\Http\Request;

use App\Http\Controllers\Controller;

use App\Post;

use Redirect,Response;

class AjaxCrudController extends Controller

{

/**

* Display a listing of the resource.

* @return \Illuminate\Http\Response

public function index()

{

return view('dtable.custom_filter');

}

public function get_custom_posts(){

$postsQuery = Post::query();

$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));

$postsQuery->whereRaw("date(posts.created_at) >= '" . $start_date . "' AND date(posts.created_at) <= '" . $end_date . "'");

}

$posts = $postsQuery->select('*');

return datatables()->of($posts)

->make(true);

}

Create Blade / View Files

In this step, we will create view/blade file to perform CRUD Operations. Lets create a blade file “custom_filter.blade.php” in “resources/views/dtable/” directory and put the following code in it respectively.

resources/views/dtable/custom_filter.blade.php

<!DOCTYPE html>
 
<html lang="en">
<head>
<!-- CSRF Token -->
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>Laravel 5.8 Custom Datatables filter and Search - W3Adda</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/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/1.9.1/jquery.js"></script>  
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
</head>
<body>
 
<div class="container">
<h2>Laravel 5.8 Custom Datatables filter and Search - W3Adda</h2>
    <div class="form-group col-md-6">
    <h5>Start Date <span class="text-danger"></span></h5>
    <div class="controls">
        <input type="date" name="start_date" id="start_date" class="form-control datepicker-autoclose" placeholder="Please select start date"> <div class="help-block"></div></div>
    </div>
    <div class="form-group col-md-6">
    <h5>End Date <span class="text-danger"></span></h5>
    <div class="controls">
        <input type="date" name="end_date" id="end_date" class="form-control datepicker-autoclose" placeholder="Please select end date"> <div class="help-block"></div></div>
    </div>
    <div class="text-left" style="
    margin-left: 15px;
    ">
    <button type="text" id="btnFiterSubmitSearch" class="btn btn-info">Submit</button>
    </div>
    
    <br>
 
    <table class="table table-bordered table-striped" id="laravel_datatable">
       <thead>
          <tr>
             <th>ID</th>
             <th>Title</th>
             <th>Body</th>
             <th>Created at</th>
          </tr>
       </thead>
    </table>
</div>
 

</body>
</html>
<script>
$(document).ready( function () {
   $.ajaxSetup({
      headers: {
          'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
      }
  });

  $('#laravel_datatable').DataTable({
         processing: true,
         serverSide: true,
         ajax: {
          url: "{{ url('dtable-custom-posts') }}",
          type: 'GET',
          data: function (d) {
          d.start_date = $('#start_date').val();
          d.end_date = $('#end_date').val();
          }
         },
         columns: [
                  { data: 'id', name: 'id', 'visible': false},
                  { data: 'title', name: 'title' },
                  { data: 'body', name: 'body' },
                  { data: 'created_at', name: 'created_at' },
               ],
        order: [[0, 'desc']]
  });
  
});

$('#btnFiterSubmitSearch').click(function(){
     $('#laravel_datatable').DataTable().draw(true);
}); 

</script>

<!DOCTYPE html>

<head>

<title>Laravel 5.8 Custom Datatables filter and Search - W3Adda</title>

</head>

<body>

<h2>Laravel 5.8 Custom Datatables filter and Search - W3Adda</h2>

<h5>Start Date <span class="text-danger"></span></h5>

</div>

</div>

<div class="text-left" style="

margin-left: 15px;

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

</div>

<br>

<thead>

<tr>

<th>Title</th>

<th>Created at</th>

</tr>

</thead>

</table>

</div>

</body>

</html>

$(document).ready( function () {

$.ajaxSetup({

headers: {

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

}

});

$('#laravel_datatable').DataTable({

processing: true,

serverSide: true,

ajax: {

url: "{{ url('dtable-custom-posts') }}",

type: 'GET',

data: function (d) {

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

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

}

columns: [

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

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

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

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

order: [[0, 'desc']]

});

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

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

});

</script>

Create Routes

After this, we need to add following routes in “routes/web.php” file along with a resource route. Lets open “routes/web.php” file and add following route.

routes/web.php

Route::get('dtable-posts-lists', 'dtable\AjaxCrudController@index');
Route::get('dtable-custom-posts', 'dtable\AjaxCrudController@get_custom_posts');

1 2	Route::get('dtable-posts-lists', 'dtable\AjaxCrudController@index'); Route::get('dtable-custom-posts', 'dtable\AjaxCrudController@get_custom_posts');

Now we are ready to run our example so lets start the development server using following artisan command –

php artisan serve

1	php artisan serve

Now, open the following URL in browser to see the output –

http://localhost:8000/dtable-posts-lists

Output 1:-

Output 2:-

Select “Start Date” and “End Date” and hit Submit button to display corresponding posts.

AngularJS

CodeIgniter

Laravel

Node Js

Wordpress

More Blog

Learn

ABOUT PAGES

Laravel Custom Datatables filter and Search