Implement Custom Search Filter in Laravel 5.8 Datatable using Ajax

If you are using Laravel framework and you are using Datatable for display data on web page in tabular format. This is because Laravel Datatable has provide lots of feature like searching, paginate data, column sorting etc. without write any line of code. But some time this Datatable default search feature is not satisfy our data searching needs. e.g. Suppose we want to get male gender data, then we have enter male in search textbox, then at that time it will load female data also, this is because "male" word has been also come in "female" word also. So at that time we want to make custom search filter in Laravel datatable for filter exact particular word data only.

For this things here we have make custom search filter in Laravel 5.8 Datatable by using Ajax. In Laravel 5.8 framework, for use Datatable in Laravel 5.8 application we have use yajra Laravel datatable package. By using this yajra Laravel Datatable package we can implement Datatable in Laravel 5.8 application. Here we will show you how to add custom search filter with yajra datatables in Laravel 5.8 framework. Suppose we want to add custom search filter like dropdown or specific fields of database then also you can do it by using Laravel 5.8 Datatable.

We all now Laravel yajra Datatable has provide by default global search feature, and in that search feature it has search entire row of table. But if we want to required search filter for only one specific table column with select box then we should have to implement custom search filter of data in our Datatable. Below you can find step by step process for how to make custom search filter in Laravel 5.8 Datatable by using yajra datatables package and Ajax jquery.

• Install Laravel 5.8 framework

• Make Database connection Laravel Framework

• Install yajra datatables package

• Create Controller

• Create View Blade file

• Set Route

• Run Laravel Application

Install Laravel 5.8 framework

First we need to download and install Laravel 5.8 framework. So we have to go command prompt, in which we have to first run composer command and then after we have to define folder in which we want to download and install Laravel 5.8 framework, and lastly we have to write following command. This command will download latest Laravel framework in define folder.

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

Make Database connection Laravel Framework

Once Laravel 5.8 framework download and install, then after we have to make database connection. For make database connection in Laravel 5.8 framework. For this we have to open .env file and in this file we need to define mysql configuration details, which you can see below.

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=

tbl_customer.sql

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom'),
(144, 'Stephen M. Menzies', 'Male', '577 Hartway Street', 'Bruie', '57325', 'United States'),
(143, 'Nikki G. Pascual', 'Female', '4291 Kinney Street', 'Agawam', '1001', 'United States'),
(141, 'Alpha A. Brookover', 'Female', '3542 Trainer Avenue', 'Kilbourne', '62655', 'United States'),
(142, 'Austin D. Salem', 'Male', '1184 Farland Street', 'Brockton', '2401', 'United States'),
(140, 'Bianca A. Carone', 'Female', '1777 Elkview Drive', 'Hialeah', '33012', 'United States'),
(139, 'Stephen M. Menzies', 'Male', '577 Hartway Street', 'Bruie', '57325', 'United States'),
(138, 'Nikki G. Pascual', 'Female', '4291 Kinney Street', 'Agawam', '1001', 'United States'),
(136, 'Alpha A. Brookover', 'Female', '3542 Trainer Avenue', 'Kilbourne', '62655', 'United States'),
(137, 'Austin D. Salem', 'Male', '1184 Farland Street', 'Brockton', '2401', 'United States'),
(145, 'Bianca A. Carone', 'Female', '1777 Elkview Drive', 'Hialeah', '33012', 'United States');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;

Install yajra datatables package

For use Datatable in Laravel 5.8 application, we have to download or install yajra datatables package in Laravel 5.8 application. For this we have go to command prompt and write following command.

composer require yajra/laravel-datatables-oracle

Once this yajra datatables package has been downloaded in Laravel 5.8 application, now we want to publish this package. For this we need to set providers and alias.
config/app.php

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

Create Controller

In Laravel 5.8 application, we have require to make controller for handle http request. For this we have to go command prompt and write following command.

php artisan make:controller CustomSearchController

This command will make CustomSearchController.php file in app/Http/Controllers folder. In this file first we have to write use DB; statement for use Database operation. In this class we have make following method.

index() - This is the root method of this class. This method has load view blade file in browser with unique country name list for load in select box. This method has also received ajax request for load data in Datatable with all data or filter data in jQuery Datatable. Below you can find source code of this controller class also.

app/Http/Controllers/CustomSearchController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class CustomSearchController extends Controller
{
    function index(Request $request)
    {
     if(request()->ajax())
     {
      if(!empty($request->filter_gender))
      {
       $data = DB::table('tbl_customer')
         ->select('CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country')
         ->where('Gender', $request->filter_gender)
         ->where('Country', $request->filter_country)
         ->get();
      }
      else
      {
       $data = DB::table('tbl_customer')
         ->select('CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country')
         ->get();
      }
      return datatables()->of($data)->make(true);
     }
     $country_name = DB::table('tbl_customer')
          ->select('Country')
          ->groupBy('Country')
          ->orderBy('Country', 'ASC')
          ->get();
     return view('custom_search', compact('country_name'));
    }
}

?>

Create View Blade file

In Laravel 5.8 application, we have to make view blade file in resources/views folder and in this folder we have make custom_search.blade.php file. In this file we have add different library like jquery, bootstrap and jQuery Datatable plugin. In this file for custom search we have define two select box and two button which you can find in source code. In this file we have also write jquery code for load data in jquery Datatable plugin. Below you can find complete source code of this file below.

resources/views/custom_search.blade.php

<html>
 <head>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Laravel 5.8 - Individual Column Search in Datatables using Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container">    
     <br />
     <h3 align="center">Laravel 5.8 - Custom Search in Datatables using Ajax</h3>
     <br />
            <br />
            <div class="row">
                <div class="col-md-4"></div>
                <div class="col-md-4">
                    <div class="form-group">
                        <select name="filter_gender" id="filter_gender" class="form-control" required>
                            <option value="">Select Gender</option>
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <select name="filter_country" id="filter_country" class="form-control" required>
                            <option value="">Select Country</option>
                            @foreach($country_name as $country)

                            <option value="{{ $country->Country }}">{{ $country->Country }}</option>

                            @endforeach
                        </select>
                    </div>
                    
                    <div class="form-group" align="center">
                        <button type="button" name="filter" id="filter" class="btn btn-info">Filter</button>

                        <button type="button" name="reset" id="reset" class="btn btn-default">Reset</button>
                    </div>
                </div>
                <div class="col-md-4"></div>
            </div>
            <br />
   <div class="table-responsive">
    <table id="customer_data" class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>Customer Name</th>
                            <th>Gender</th>
                            <th>Address</th>
                            <th>City</th>
                            <th>Postal Code</th>
                            <th>Country</th>
                        </tr>
                    </thead>
                </table>
   </div>
            <br />
            <br />
  </div>
 </body>
</html>

<script>
$(document).ready(function(){

    fill_datatable();

    function fill_datatable(filter_gender = '', filter_country = '')
    {
        var dataTable = $('#customer_data').DataTable({
            processing: true,
            serverSide: true,
            ajax:{
                url: "{{ route('customsearch.index') }}",
                data:{filter_gender:filter_gender, filter_country:filter_country}
            },
            columns: [
                {
                    data:'CustomerName',
                    name:'CustomerName'
                },
                {
                    data:'Gender',
                    name:'Gender'
                },
                {
                    data:'Address',
                    name:'Address'
                },
                {
                    data:'City',
                    name:'City'
                },
                {
                    data:'PostalCode',
                    name:'PostalCode'
                },
                {
                    data:'Country',
                    name:'Country'
                }
            ]
        });
    }

    $('#filter').click(function(){
        var filter_gender = $('#filter_gender').val();
        var filter_country = $('#filter_country').val();

        if(filter_gender != '' &&  filter_gender != '')
        {
            $('#customer_data').DataTable().destroy();
            fill_datatable(filter_gender, filter_country);
        }
        else
        {
            alert('Select Both filter option');
        }
    });

    $('#reset').click(function(){
        $('#filter_gender').val('');
        $('#filter_country').val('');
        $('#customer_data').DataTable().destroy();
        fill_datatable();
    });

});
</script>

Set Route

Once all controller and view file code is ready, after this we have need to set route of controller method. For this we have to open routes/web.php file and write following code.

routes/web.php

Route::resource('customsearch', 'CustomSearchController');

Run Laravel Application

Lastly, We need to start Laravel server, for this we have to go command prompt and write following command.

php artisan serve

This command will start Laravel application and return base url of Laravel application. For run above example, we have to type http://127.0.0.1:8000/customsearch and you can run above example and check the feature of custom search in Laravel 5.8 Datatable using Ajax with yajra datatables package.