Laravel Datatable Dynamic Filter (Advance Filter) (phpMyAdmin Style)

course.js

var Course = function () {

    var lookupTable = function () {
		var oTable = $('#course_table').DataTable({
			dom: 'Bfrtip',
			processing: true,
			serverSide: true,
			"dom": "<'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'>r>t<'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>",
		    "bStateSave": true, // save datatable state(pagination, sort, etc) in cookie.
		    "lengthMenu": [
		    	[10, 15, 20, -1],
		        [10, 15, 20, "All"] // change per page values here
		    ],
		    // set the initial value
		    "pageLength": 10,
		    ajax: {
		    	url: lookup_route,
		    	data: function (d) {
		    		d.course_name = $('input[name=course_name]').val();
		    		d.course_name_operator = $('#course_name_operator').val();

		    		d.course_code = $('input[name=course_code]').val();
		    		d.course_code_operator = $('#course_code_operator').val();
		    	}
		    },
		    "drawCallback": function(settings) {
		    	// console.log(settings.json);
		    	$('[data-toggle="tooltip"]').tooltip()
		    },
		    columns: [
			    { data: 'rownum', name: 'rownum' },
			    { data: 'name', name: 'name' },
			    { data: 'code', name: 'code' },
			    { data: 'description', name: 'description' },			   
			    {data: 'action', name: 'action', orderable: false, searchable: false}
		    ]
		});

		$(document).on('click','.search-form', function(e) {
			oTable.draw();
			e.preventDefault();
		});

		$(document).on('keypress','.filterControl', function (e) {
            var code = e.keyCode || e.which;
            if (code == 13) {
                oTable.draw();
                e.preventDefault();
            }
        });

		$(document).on('click', '.filter-reset', function(e) {
			$('.select2me').each(function () {
				$(this).val($(this).find('option:first-child').val()).trigger('change.select2');
	        });

	        $('.searchOperator').trigger('change');
	        $(".filterControl").val("");
			$('#select_filter').trigger("reset");
			oTable.draw(false);
		});

		$(document).on('click', '.delete-course', function(event) {
			event.preventDefault();
			course_id =  $(this).data('id');
			route 	  = delete_route.replace('-1', course_id);

			bootbox.confirm({
				title: "Delete course?",
				message: "Are you sure want to delete it?",
				buttons: {
					cancel: {
						label: '<i class="fa fa-times"></i> Cancel',
						className: 'btn-danger'
					},
					confirm: {
						label: '<i class="fa fa-check"></i> Confirm',
						className: 'btn-success'
					}
				},
				callback: function (result) {
			        // console.log('This was logged in the callback: ' + result);
			        if (result) {
			        	// alert(course_id);
			        	$.ajax({
			        		url: route,
			        		type:"POST",
			        	    // dataType:'json',
			        	    data: { '_method' : 'DELETE' },
			        	    success: function(data)
			        	    {
			        	    	if (data.success) 
			        	    	{
			        	    		$('.info-message').html(data.message);
			        	    		info.show().delay(5000).fadeOut();
			        	    		oTable.draw(false);
			        	    		App.scrollTo(info, -200);
			        	    	}
			        	    	else 
			        	    	{
			        	    		$('.error-message').html("Something went wrong.");
			        	    		error.show().delay(4000).fadeOut();
			        	    	}
			        	    	// location.reload();
			        	    },
			        	    error: function(data)
			        	    {
			        	    	$('.error-message').html("Something went wrong.");
			        	    	error.show().delay(4000).fadeOut();
			        	    	console.log('error');
			        	    }
			        	});
			        }
			    }
			});	
		});


		$(document).on('change','.searchOperator',function(){
			var input_id = $(this).attr("id");
			//alert(input_id); 
			input_id = input_id.split("_operator");

			var input_val = $(this).val();
			//alert(input_id[0]);
			//alert(input_val);
			if(input_val == "=''" || input_val == "!=''")
	  		{
	  			$("#"+input_id[0]).val('');
	  			$("#"+input_id[0]+"_div").hide();
	  			$("#"+input_id[0]+"_range").hide();	
	 			
	  		}
	  		else if(input_val == "BETWEEN" || input_val == "NOT BETWEEN")
	  		
	  		{
	  			$("#"+input_id[0]).val('');
	  			$("#"+input_id[0]+"_div").hide();
	  			$("#"+input_id[0]+"_range").show();
	  		}
	  		else{
	  			$("#"+input_id[0]+"_div").show();
	  			$("#"+input_id[0]+"_range").hide();	
	  		}			
		});

		// to focus on Name field (by default) on page load
		$('#course_name').focus();
	}
    
    return {

        //main function
        initLookupTable: function () {
        	lookupTable();
        	// pageActions();
        },
    }
}

CourseController.php

<?php
namespace App\Http\Controllers;

use DB;
use Config;
use App\Models\Course;
use Illuminate\Http\Request;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Facades\Validator;

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

    public function lookup()
    {
        // Array having field name as key and column name as value for filter
        $this->filterFieldArray = [
            'course_name' => 'name',
            'course_code' => 'code',  
        ];
        DB::statement(DB::raw('set @rownum=0'));
        $course = Course::select([
            '*',
            DB::raw('@rownum  := @rownum  + 1 AS rownum')  /* increment rownum by 1, for each record */
        ]);
        return Datatables::of($course)
        ->filter(function ($query) {
            foreach ($this->filterFieldArray as $field => $column) {

                if(request($field."_operator")=="=''" || request($field."_operator")=="!=''")
                {
                    datatableFilterQuery($query, $column, request($field.'_operator'), request($field), request($field.'_from'), request($field.'_to'));  
                }
                else if (request()->has($field) && (!empty(request($field) ) || !empty( request($field.'_from'))  )) 
                {
                    datatableFilterQuery($query, $column, request($field.'_operator'), request($field), request($field.'_from'), request($field.'_to'));
                }
            }
        }) 
        ->addColumn('action', function ($course) {
            return '<a href="'. route('course.edit', $course->id) .'" data-toggle="tooltip" title="Edit"  class="btn btn-sm btn-primary edit-course" data-id="'.$course->id.'"><i class="fa fa-edit"></i> </a> 
            <a href="javascript:void(0);" data-toggle="tooltip" title="Delete" class="btn btn-sm btn-danger delete-course" data-id="'.$course->id.'"><i class="fa fa-trash"></i></a> 
            ';
        })
        ->toJson();
        // ->make(true);

    }
  
}

Helper.php

<?php


/*Filter Select Options*/
if (!function_exists('filterOptions')) {
    function filterOptions($optArr)
    {
        $options = '';

        if (isset($optArr)) {
            foreach ($optArr as $opt) {  
                                                
                switch ($opt) {
                    case 'e':
                        $options .= '<option value="=">Equals</option>';
                        break;
                    case 'ne':
                        $options .= '<option value="!=">Not Equal</option>';
                        break;
                    case 'gt':
                        $options .= '<option value=">">Greater than</option>';
                        break;
                    case 'gte':
                        $options .= '<option value=">=">Greater than or equal to</option>';
                        break;
                    case 'lt':
                        $options .= '<option value="<">Less than</option>';
                        break;
                    case 'lte':
                        $options .= '<option value="<=">Less than or equal to</option>';
                        break;
                    case 'lk':
                        $options .= '<option value="LIKE \'%...%\'">Contains</option>';
                        break;
                    case 'in':
                        $options .= '<option value="IN (...)">IN (...)</option>';
                        break;
                    case 'nin':
                        $options .= '<option value="NOT IN (...)">NOT IN (...)</option>';
                        break;
                    case 'bt':
                        $options .= '<option value="BETWEEN" id="between_range">BETWEEN</option>';
                        break;
                    case 'nbt':
                        $options .= '<option value="NOT BETWEEN">NOT BETWEEN</option>';
                        break;
                    case 'n':
                        $options .= '<option value="=\'\'" id="blank">IS BLANK</option>';
                        break;
                    case 'nn':
                        $options .= '<option value="!=\'\'">IS NOT BLANK</option>';
                        break;
                }
            }

            echo $options;
        }
    }
}


/*Query Filter*/
if (!function_exists('datatableFilterQuery')) {

    function datatableFilterQuery($query, $column, $operator, $input = null, $input_from = null, $input_to = null, $rawQuery = false)
    {
        if( isset($operator) && $operator != "" ){
            switch ($operator) {
                case ">"    :   return $query->where( $column, $operator, $input); 
                case ">="   :   return $query->where( $column, $operator, $input);
                case "<"    :   return $query->where( $column, $operator, $input);
                case "<="   :   return $query->where( $column, $operator, $input);
                case "="    :   
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' = "' . $input . '"');
                    } else {
                        return $query->where( $column, $operator, $input); 
                    }
                    
                case "!="   :   
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' != "' . $input . '"');
                    } else {
                        return $query->where( $column, $operator, $input);
                    }

                case "IN (...)" :
                    $input = explode(',', $input);
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' IN ("' .implode('","', $input). '")');
                    } else {
                        return $query->whereIn($column, $input);
                    }                    
                
                case "NOT IN (...)" :
                    $input = explode(',', $input);
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' NOT IN ("' .implode('","', $input). '")');
                    } else {
                        return $query->whereNotIn($column, $input);
                    }
                
                case "=''"  :   
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' IS NULL ');
                    } else {
                        return $query->whereNull($column);
                    }

                case "!=''" :   
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' IS NOT NULL ');
                    } else {
                        return $query->whereNotNull($column);
                    }
                
                case "LIKE '%...%'" :   
                    if ($rawQuery) {
                        return $query->whereRaw($column. ' like "%' . $input . '%"');
                    } else {
                        return $query->where($column, 'like', "%" . $input . "%");
                    }
                
                case "BETWEEN"  :
                $input = array($input_from, $input_to);
                return $query->whereBetween($column, $input);
                
                case "NOT BETWEEN"  :
                $input = array($input_from, $input_to);
                return $query->whereNotBetween($column, $input);
            }
        }
    }
}

lookup.blade.php

@extends('layouts.master') 
@section('content')
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
	<!-- Content Header (Page header) -->
	<div class="content-header">
		<div class="container-fluid">
			<div class="row mb-2">
				<div class="col-sm-6">
					<h1 class="m-0 text-dark">Course</h1>
				</div>
				<!-- /.col -->
				<div class="col-sm-6">
					<ol class="breadcrumb float-sm-right">
						<li class="breadcrumb-item"><a href="{{ route('home') }}">Home</a></li>
						<li class="breadcrumb-item"><a href="{{ route('coursebatch.menu') }}">Course</a></li>
						<li class="breadcrumb-item active">Course</li>
					</ol>
				</div>
				<!-- /.col -->
			</div>
			<!-- /.row -->

			<!-- Alert Box -->
			<div class="alert alert-danger alert-dismissible" style="display: none;">
				<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
				<span class="error-message"></span>
			</div>
			<div class="alert alert-info alert-dismissible" style="display: none;">
				<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
				<span class="info-message"></span>
			</div>
			<!-- /.Alert Box -->
		</div>
		<!-- /.container-fluid -->
	</div>
	<!-- /.content-header -->

	<!-- Main content -->
	<section class="content">
		<div class="container-fluid">
			<div class="row">
				<div class="col-12">
					<div class="card card-warning animated slideInLeft delay-1s">
						<div class="card-header">
							<h3 class="card-title"><i class="fas fa-filter"></i> Filter</h3>

							<div class="card-tools">
								<button type="button" class="btn btn-tool" data-widget="collapse"><i class="fa fa-minus"></i>
								</button>
							</div>
							<!-- /.card-tools -->
						</div>
						<!-- /.card-header -->
						<div class="card-body">
							<form id="select_filter" action="{{ route('course.export') }}" method="POST" >
								@csrf
							<div class="row">
								<div class="col-md-6">
									
									<div class="form-group row">
										<label class="col-sm-3 col-form-label" for="course_name">Course Name</label>
										<div class="col-md-4">
				   							<select name="course_name_operator" id="course_name_operator" class="select2me form-control  searchOperator">
				   								{{ filterOptions(["e", "ne", "n", "nn", "lk"]) }}
				   								
				   							</select>
				   						</div>
				   						
										<div class="col-sm-4" id="course_name_div">
											<input type="text" name="course_name" value="" class="form-control filterControl" id="course_name" placeholder="Course Name" required>
										</div>
									</div>
									
								</div>
								
								<div class="col-md-6">
									
									<div class="form-group row">
										<label class="col-sm-3 col-form-label" for="course_code"> Course Code</label>
										<div class="col-md-4">
				   							<select name="course_code_operator" id="course_code_operator" class="select2me form-control  searchOperator">
				   								{{ filterOptions(["e", "ne", "n", "nn", "lk"]) }}
				   							</select>
				   						</div>
				   						
										<div class="col-sm-4" id="course_code_div">
											<input type="text" name="course_code" value="" class="form-control filterControl" id="course_code" placeholder="Course Code" >
										</div>
									</div>
								</div>
							</div>
							</form>

						</div>
						<!-- /.card-body -->

						<div class="card-footer text-center">
							<button type="reset" class="btn btn-danger filter-reset"><i class="fas fa-undo"></i> Reset</button>
							<button type="button" class="btn btn-success search-form"><i class="fas fa-search"></i> Search</button>
						</div>
						<!-- /.card-body -->
					</div>
					<!-- /.card -->
					<div class="card animated slideInRight delay-1s">
						<div class="card-header">
							<h3 class="card-title"><i class="fas fa-clipboard-list"></i> Lookup</h3>
							<div class="card-tools">
								<a  href="{{ route('course.create') }}" class="btn btn-sm btn-primary add-course" data-toggle="tooltip" title="Add course"> 
									<i class="fa fa-plus"></i> 
								</a>
								<a href="javascript:void(0)" class="btn btn-sm btn-warning export" data-toggle="tooltip" title="Export">
									<i class="fa fa-download"></i>
								</a>
							</div>
						</div><!-- /.card-header -->
						<div class="card-body">
							<div class="tab-pane active" id="lookup">
								<table class="table table-bordered" id="course_table" style="width: 100%">
									<thead>
										<tr>
											<th style="width: 8%">#</th>
											<th>Course Name</th>
											<th>Course Code</th>
											<th>Description</th>
											<th style="width: 10%">Action</th>
										</tr>
										</tr>
									</thead>
									<tbody>
									</tbody>
								</table>
							</div>
						</div><!-- /.card-body -->
					</div>
					<!-- ./card -->
				</div>
			</div>
			<div class="row">
				<div class="col-md-6">
					
					
				</div>
				<div class="col-md-6">
				</div>
			</div>
			<!-- /.row -->
		</div>
		<!-- /.container-fluid -->
	</section>
	<!-- /.content -->
</div>
<!-- /.content-wrapper -->

@endsection
@section('css')
<!-- DataTables -->
<link rel="stylesheet" href="{{ asset('/assets/dist/plugins/datatables/dataTables.bootstrap4.css') }}">
<link rel="stylesheet" href="{{ asset('/assets/dist/plugins/select2/select2.min.css') }}">
@stop

@section('javascript')

<!-- DataTables -->
<script src="{{ asset('/assets/dist/plugins/datatables/jquery.dataTables.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/datatables/dataTables.bootstrap4.js') }}"></script>
<!-- Sparkline -->
<script src="{{ asset('/assets/dist/plugins/sparkline/jquery.sparkline.min.js') }}"></script>
<!-- jvectormap -->
<script src="{{ asset('/assets/dist/plugins/jvectormap/jquery-jvectormap-1.2.2.min.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/jvectormap/jquery-jvectormap-world-mill-en.js') }}"></script>

<!-- ChartJS 1.0.2 -->
<script src="{{ asset('/assets/dist/plugins/chartjs-old/Chart.min.js') }}"></script>

<!-- jquery validation -->
<script src="{{ asset('/assets/dist/plugins/jquery-validation/js/jquery.validate.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/jquery-validation/js/additional-methods.min.js') }}"></script>
<script src="{{ asset('/assets/dist/plugins/select2/select2.full.min.js') }}"></script>
<!-- Page JS -->
<script src="{{ asset('/assets/js/course.js') }}"></script>

<script type="text/javascript">

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

	var delete_route = '{!! route('course.destroy', -1) !!}';
	var lookup_route = '{!! route('course.data') !!}';

	Course.initLookupTable();
	Course.setTableHighlight();

</script>
@stop

web.php

<?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('currency/lookup', 'CurrencyController@lookup')->name('currency.data');

Hi @kasirye to load helper file follow this link https://laravel-news.com/creating-helpers.

Thank you @MubinSayed the link has been really helpful, I appreciate. Am having challenges implementing the range filter on my blade view

Hi @kasirye, added a new public repository https://github.com/MubinSayed/laravel-datatable-advance-filter-phpmyadmin-style for the above code. Clone it and let me know your thoughts. :)

Great work @MubinSayid

I now understand

Have been using Yajra Datatables as a service and it was alittle difficult to implement something like this. Every time my entire page would reload whenever I searched.

Have you used Datatables as a service before.

I have a table that has over 20 columns, I think the filter will just cover the whole page without the table being visible... Am not the best at GUI/Frontend solutions to make it appear more organised or beautiful like yours 😂😂 .

Am also interested in the select checkboxes, export buttons, column visibility, delete. So if the column is not visible then it shouldn't appear on the advanced filter and vice versa.