DataTables Server-side Processing in Laravel

---

In this tutorial, I will show you the easiest way to implement DataTables 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.

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

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

 <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>

The 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

Route::post('allposts', 'PostController@allPosts' )->name('allposts');

Now I am using Laravel Eloquent for simplicity and code size reduction. So we need to create a Post model.

The Post model code is given 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. Please consider learning that before proceeding this tutorial.

Now before we start coding our controller we need to know that Datatable will post a lot of data to controller function during Ajax request. I found that function by using a plugin in firefox called Firebug. If you are a web developer and use ajax often then I extremely recommend Firebug.

For this tutorial, we need to look at only 6 post requests.

1. 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.
2. 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).
3. order[0]column: Column to which ordering should be applied. This is an index reference to the columns array of information that is also submitted to the server.
4. order[0]dir: Ordering direction for this column. It will be asc or desc to indicate ascending ordering or descending ordering, respectively.
5. search[value]: The Global search value.
6. 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'] = "&emsp;<a href='{$show}' title='SHOW' ><span class='glyphicon glyphicon-list'></span></a>
                                          &emsp;<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); 
        
    }

 

Now let's see the output image.

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.

The above sample image is the actual screenshot of my shareurcodes admin panel. So you guys can ensure that above code will work 100%. I also made a tutorial on DataTable Server-side Processing in Codeigniter. But since CodeIgniter follow traditional MVC and does not has support for ORM the number of codes is higher than Laravel. If anybody wants to know the easiest way to implement datatables with CodeIgniter please comment below and if anybody has any suggestions or doubts or need any help comment below.