How to implement DataTables server-side in laravel

by

In this post, I will be show you the easiest  way to implement  DataTables  server side processing in Laravel. Here I will show you how to fetch data  using ajax in laravel and render that data in DataTable with searching, Pagination and sorting with server side processing.

This post is an extension to a previous post on DataTables, where we initiated data tables with basic initialization. As many readers suggested to make a tutorial on DataTables server-side. . In this post we have explain step by step. Because Jquery DataTables will add some advance features like quick search of table data, it will make automatic pagination without write of code, table column ordering, sorting of table column and many more.

Now before we start coding include Datatables CSS file and Javascript files from CDN in your view page as follows.

1
2
3
4
5
 
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css"/>
 
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>
 





Now let’s understand what all tasks we need to do

  1. We need to limit the size of the table. (By default 10,25,50 or 100 entries)
  2. Now Implement search functionality and sort.
  3. The Pagination task.

All above task will be done in the controller and it will be explained later in this tutorial.

Now let’s start coding.

In the view page code for HTML table is given below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
<div class="panel-body">
        <div class="panel-body">  
         <div class="row">        
         <table class="table table-bordered" id="institute_table" style="width:100%">
 
                      <thead>
 
                             <th>Id</th>
 
                             <th>Brand Name</th>
                             <th>Model Name</th>
<th>Description</th>
<th>Price</th>
<th>Action</th>
 
                        </thead>        
 
         </table>
      </div>
        </div>
 
       </div>
 

 

Now we need to add javascript code, you can add that code in your view file or any javascript file which is include with this view. Please copy the given code and paste in your file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
<script>
 
      $(document).ready(function(){
 
        // Data table for serverside
 
       $('#institute_table').DataTable({
 
            "processing": true,
 
            "serverSide": true,
 
            "ajax":{
 
                     "url": "{{ url('productList') }}",
 
                     "dataType": "json",
 
                     "type": "POST",
 
                     "data":{ _token: "{{csrf_token()}}",route:'products'}
 
                   },
 
            "columns": [
 
                { "data": "id" },
 
                { "data": "brand_name" },
{ "data": "model_name" },
{ "data": "description" },
{ "data": "price" },
 
                { "data": "action" }
 
            ],
 
            aoColumnDefs: [
 
            {
 
               bSortable: false,
 
               aTargets: [ -1 ]
 
            }
 
          ]  
 
 
 
        });
});
</script>
 

 

Note:- Don’t forgot to pass CSRF Token along with ajax POST request as above. Otherwise ajax occurs internal server 500, Laravel checks  CSRF Token in all post request by default ensure maximum protection.

Now we need to create routes for list data and load view

1
2
3
4
 
Route::get('products','DemoController@products');
Route::post('productList','DemoController@productList');
 

Now create product model file, Please copy below code and paste

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<?php
 
namespace App;
 
use Illuminate\Database\Eloquent\Model;
 
class Prodcut extends Model
{
    
protected $table = 'proucts';
protected $fillable = ['brand_name','model_name','price','description'];
}
 

 

Note:  If you don’t know the basic concept of Laravel Eloquent ORM then all controller code may find little confusing to you. Please consider learning that before proceeding this tutorial.



Now create Demo controller file in app/Http/Controllers/DemoController.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
<?php
namespace App\Http\Controllers;
 
 
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use PDF;
use App\Products;
 
 
class DemoController extends Controller
{
 
  
    function products(Request $request){
        return view('server_side_table');
    }
    function productList(Request $request){
 
       $columns = array(
            0 =>'id',
            1 =>'brand_name',
            2 =>'model_name',
            3 =>'description',
            4 =>'price',
        );  
        $totalData = Products::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')))
        {            
            $institutes = Products::offset($start)
                         ->limit($limit)
                         ->orderBy($order,$dir)
                         ->get();
        }else {
            $search = $request->input('search.value');
 
            $institutes =  Products::where('id','LIKE',"%{$search}%")
                            ->orWhere('brand_name', 'LIKE',"%{$search}%")
->orWhere('model_name', 'LIKE',"%{$search}%")
->orWhere('price', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();
 
            $totalFiltered = Products::where('id','LIKE',"%{$search}%")
                             ->orWhere('brand_name', 'LIKE',"%{$search}%")
->orWhere('model_name', 'LIKE',"%{$search}%")
->orWhere('price', 'LIKE',"%{$search}%")
                             ->count();
        }
        $data = array();
        if(!empty($institutes))
        {
            foreach ($institutes as $key=>$institute)
            {
                
                $nestedData['id'] = $key+1;
                $nestedData['brand_name'] = $institute->brand_name;
                $nestedData['model_name'] = $institute->model_name;
                $nestedData['price'] = $institute->price;
                $nestedData['description'] = $institute->description;
                $nestedData['action'] = '<a href="#" class="del"><span class="glyphicon glyphicon-trash"></span>
</a><a href="#" class="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);
     }          
        
}
 
?>
 

 

 

View Demo
 

I hope you might have understood it properly, keep reading our other blog posts for more coding tricks.

Thanks!!

Leave a Comment