Skip to content

Instantly share code, notes, and snippets.

@MubinSayed

MubinSayed/CourseController.php

Last active April 25, 2021 05:11
Show Gist options
  • Save MubinSayed/bb7147dbe7d653d2535087100b14e640 to your computer and use it in GitHub Desktop.
Save MubinSayed/bb7147dbe7d653d2535087100b14e640 to your computer and use it in GitHub Desktop.
Download ZIP
Laravel Datatable Dynamic Filter (Advance Filter) (phpMyAdmin Style)
Raw
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();
},
}
}
Raw
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);
}
}
Raw
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);
}
}
}
}
Raw
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
Raw
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');
@MubinSayed
Copy link
Author

MubinSayed commented Apr 24, 2021

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

@kasirye
Copy link

kasirye commented Apr 24, 2021

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

@MubinSayed
Copy link
Author

MubinSayed commented Apr 25, 2021

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. :)

@kasirye
Copy link

kasirye commented Apr 25, 2021

Great work @MubinSayid
Screenshot 2021-04-25 at 07 51 38

I now understand

@kasirye
Copy link

kasirye commented Apr 25, 2021

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.

@kasirye
Copy link

kasirye commented Apr 25, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment