How to do Serverside Listing and Filtering using JQuery Datatables in Laravel
The Jquery Datatables is a very handy utility tool to perform Serverside Listing, Pagination, Search, and Ordering. So in this post, you’ll learn How to use JQuery Datatables in Laravel.
For this, I’ll take an example of posts that have to be listed and filtered by date range. The First thing is to create a Controller and name it as DatatableController. This controller has two methods on is the index() method to show the blade view and to retrieve JSON data there is the method posts().
In app\Http\Controllers\DatatableController.php
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use App\Models\PostModel;
use Illuminate\Http\Request;
use Symfony\Component\Console\Input\Input;
class DatatableController extends Controller{
public function index( Request $request ){
$info = [];
return view( 'datatables.list', $info );
}
public function posts( Request $request ){
$info = [
'draw' => $request->draw,
'data' => [],
'total' => 0,
];
$search = $request->input('search.value');
$posts = PostModel::orWhere( function($query) use ( $search ) {
$query->where( "name", "LIKE", "%".$search."%" )->where( "slug", "LIKE", "%".$search."%" );
} )->dateFilter( $request->from_date, $request->to_date )->take( $request->length )->skip( $request->start )->get();
$info['total'] = PostModel::orWhere( function($query) use ( $search ) {
$query->where( "name", "LIKE", "%".$search."%" )->where( "slug", "LIKE", "%".$search."%" );
} )->dateFilter( $request->from_date, $request->to_date )->count();
$sl_no_counter = ( $request->start == 0 )? 1 : $request->start+1;
foreach( $posts as $post ){
$post->sl_no = $sl_no_counter;
$sl_no_counter++;
}
$info['data'] = $posts;
return $info;
}
}
The Datatable excepts and JSON response formatted like shown below.
{
'data' : [
// list of items
],
'draw' : '', //sent by datatable itself to server
'total': '' //The total number of row exists in the table this is for pagination
}
Now for the filtering list, I’ll be scoping queries in Laravel PostModel and filter only those which are under the date range.
public function scopeDateFilter( $query, $from_date=null, $to_date=null ){
if( !empty( $from_date ) ){
$from_date = date('Y-m-d 00:00:01', strtotime( $from_date ) );
$to_date = ( !empty( $to_date ) )? date('Y-m-d 23:59:59', strtotime( $to_date ) ) : date('Y-m-d 23:59:59' );
$query->whereBetween( 'created_at', [ $from_date, $to_date ] );
}
return $query;
}
Complete PostModel Code
In app\Models\PostModel.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class PostModel extends Model
{
protected $table = 'posts';
protected $primaryKey = 'post_id';
protected $fillable = ['name', 'slug', 'description' ];
protected $dates = ['deleted_at'];
// dateFilter
public function scopeDateFilter( $query, $from_date=null, $to_date=null ){
if( !empty( $from_date ) ){
$from_date = date('Y-m-d 00:00:01', strtotime( $from_date ) );
$to_date = ( !empty( $to_date ) )? date('Y-m-d 23:59:59', strtotime( $to_date ) ) : date('Y-m-d 23:59:59' );
$query->whereBetween( 'created_at', [ $from_date, $to_date ] );
}
return $query;
}
}
Complete JQuery Snippet
In resources\views\datatables\list.blade.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Datatables</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css">
<script src="http://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
</head>
<body>
<div class="container">
<h2 class="text-center">Laravel Datatables</h2>
<div class="table-responsive">
<hr>
<div class="row">
<div class="col-md-3">
<label for="">From Date</label>
<input type="date" id="from_date" value="" >
</div>
<div class="col-md-3">
<label for="">To Date</label>
<input type="date" id="to_date" value="" >
</div>
<div class="col-md-3">
<button type="button" class="btn btn-info" onclick="reload_table()" >Filter</button>
</div>
</div>
<hr>
<table id="posts-table" class="table table-bordered">
<thead>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
<script>
$(function() {
var drawer_count = 1;
$('#posts-table').DataTable({
"oLanguage": {
"sProcessing": '<span>Please wait ...</span>'
},
"pagingType": "simple_numbers",
"paging": true,
"lengthMenu": [
[10, 25, 50],
[10, 25, 50]
],
"processing": true,
"serverSide": true,
"ordering": false,
"ajax": {
"type": "GET",
"url": "{{ url('datatables/posts') }}",
"data": function(d) {
d.from_date = document.getElementById('from_date').value;
d.to_date = document.getElementById('to_date').value;
},
"dataFilter": function(data) {
drawer_count++;
var json = jQuery.parseJSON(data);
json.draw = json.draw;
json.recordsTotal = json.total;
json.recordsFiltered = json.total;
json.data = json.data;
$('#list_table_processing').css('display', 'none');
return JSON.stringify(json); // return JSON string
}
},
"columns": [
{"title": "#", "data": "sl_no", "name": "sl_no", "visible": true, "searchable": true},
{"title": "Title", "data": "name", "name": "name", "visible": true, "searchable": true},
{"title": "Slug", "data": "slug", "name": "slug", "visible": true, "searchable": true},
{"title": "Description", "data": "description", "name": "description", "visible": true, "searchable": true},
],
});
});
function reload_table() {
$('#posts-table').DataTable().ajax.reload();
}
</script>
</body>
</html>
Output
Ajax Fetching List of items using JQuery Datatables in Laravel
Video Tutorial
Conclusion
We have reached the end of our post on How to do Serverside Listing and Filtering using JQuery Datatables in Laravel. Support us by sharing this post.
Related Posts
- Laravel Jobs and Queues – Configuring, Sending Mail, Dispatching Jobs
- Laravel Emails | Sending emails with multiple attachments
Summary
Reviewer
The Code Learners
Review Date
Reviewed Item
How to do Serverside Listing and Filtering using JQuery Datatables in Laravel
Author Rating
Software Name
Laravel Framework
Software Name
Windows Os, Mac Os, Ubuntu Os
Software Category
Web Development
