Laravel Datatable with 1 million records
In this article, I will show an easiest way to implement DataTable jQuery Plugin with remote server side processing in Laravel. Here I will show you how to fetch data from remote MySQL database through ajax in Laravel. For those who don’t here about Datatables, DataTables is a table enhancing plug-in for the jQuery Javascript library that helps in adding sorting, paging and filtering abilities to plain HTML tables with minimal effort. The main goal is to enhance the accessibility of data in normal HTML tables.
Github Repository:- Laravel Datatable
Now before we start coding include Datatables CSS file and Javascript files from CDN in your view page as follows.
<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"><link href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.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><script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
Now let’s understand what all tasks we need to do
- We need to limit the size of the table. (By default 10,25,50 or 100 entries)
- Now Implement search functionality.
- The Pagination task.
All above task will be done in the controller and it will be explained later in this article.
Now let’s start coding.
In the view page code for HTML table is given below.
<div class="row">
<div class="col-md-12">
<table class="table table-bordered" id="posts">
<thead>
<th>Id</th>
<th>Title</th>
<th>Body</th>
<th>Created At</th>
<th>Options</th>
</thead>
</table>
</div>
</div>Add javascript code is given below.
<script>
$(document).ready(function () {
$('#posts').DataTable({
"processing": true,
"serverSide": true,
"ajax":{
"url": "{{ url('allposts') }}",
"dataType": "json",
"type": "POST",
"data":{ _token: "{{csrf_token()}}"}
},
"columns": [
{ "data": "id" },
{ "data": "title" },
{ "data": "body" },
{ "data": "created_at" },
{ "data": "options" }
]
});
});
</script>Note: Do not forget to pass CSRF Token along with ajax POST request as above. Otherwise, internal server error 500 will occur. This is because Laravel checks CSRF token in all POST controller functions by default to ensure maximum protection.
Now the code for post routes in routes/web.php
If you are using laravel 5,6 or 7 than use below code in your routes/web.php
Route::post('allposts', 'PostController@allPosts' )->name('allposts');if you are using laravel latest version that is Laravel version 8, which was released on September 8, 2020 than use below code in your routes/web.php
use App\Http\Controllers\PostController;// PostControllerRoute::post('allposts', [PostController::class, 'allPosts']);
The Post model code is given below.
if you are have created model than use below command and run in your terminal.(if you already modal created you can skip this)
php artisan make:model Postafter sucessfully runnig command you get one file inside your app directory and that filename is Post.php and you get boilerplate code that shown in below.
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
}Note: If you don’t know the basic concept of Laravel Eloquent ORM then all controller code may find little confusing to you.
this article, we need to look at only 6 post requests.
- length: Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number unless the server has fewer records to return.
- start: Paging first record indicator. This is the start point in the current data set (0 index based — i.e. 0 is the first record).
- order[0]column: Column to which ordering should be applied. This is an index reference to the
columnsarray of information that is also submitted to the server. - order[0]dir: Ordering direction for this column. It will be
ascordescto indicate ascending ordering or descending ordering, respectively. - search[value]: The Global search value.
- draw: Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables (Ajax requests are asynchronous and thus can return out of sequence).
Now the code for allPost function in PostController.
public function allPosts(Request $request)
{
$columns = array(
0 =>'id',
1 =>'title',
2=> 'body',
3=> 'created_at',
4=> 'id',
);
$totalData = Post::count();
$totalFiltered = $totalData;
$limit = $request->input('length');
$start = $request->input('start');
$order = $columns[$request->input('order.0.column')];
$dir = $request->input('order.0.dir');
if(empty($request->input('search.value')))
{
$posts = Post::offset($start)
->limit($limit)
->orderBy($order,$dir)
->get();
}
else {
$search = $request->input('search.value');
$posts = Post::where('id','LIKE',"%{$search}%")
->orWhere('title', 'LIKE',"%{$search}%")
->offset($start)
->limit($limit)
->orderBy($order,$dir)
->get();
$totalFiltered = Post::where('id','LIKE',"%{$search}%")
->orWhere('title', 'LIKE',"%{$search}%")
->count();
}
$data = array();
if(!empty($posts))
{
foreach ($posts as $post)
{
$show = route('posts.show',$post->id);
$edit = route('posts.edit',$post->id);
$nestedData['id'] = $post->id;
$nestedData['title'] = $post->title;
$nestedData['body'] = substr(strip_tags($post->body),0,50)."...";
$nestedData['created_at'] = date('j M Y h:i a',strtotime($post->created_at));
$nestedData['options'] = " <a href='{$show}' title='SHOW' ><span class='glyphicon glyphicon-list'></span></a>
 <a href='{$edit}' title='EDIT' ><span class='glyphicon glyphicon-edit'></span></a>";
$data[] = $nestedData;
}
}
$json_data = array(
"draw" => intval($request->input('draw')),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalFiltered),
"data" => $data
);
echo json_encode($json_data);
}Note: The column array is used to identify which MySQL database table column should be sorted in ascending or descending order. They are the actual names of the database columns. Their count must be equal to Datatables columns count.
Hurray we integrate datatable with laravel Now you can simply go to your view url in your browser and see the output.
if you facing any kind of problem let me know in commet section
