I. What is bootstrap datatable
DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table. The more details and examples can be found at the following URL : https://datatables.net/
II. How to mix them up
a. Creating a new laravel project
Project creation will not be described here but basic steps can be found easily at Laravel's home page's installation section :
URL : https://laravel.com/docs/master/
I myself prefred the installation via composer
composer create-project --prefer-dist laravel/laravel MyProject
b. Install datatables bootstrap :
The fastest way to get started with DataTables is to use the download builder. With this tool you can build a customised DataTables package, selecting the software that you wish to use in various combinations of styling and related packages to suit your needs.
URL : https://datatables.net/download/index
Library size is important in web applications - the more code there is, the longer it takes to download and parse for the end user's browser. As such, only libraries that you actually need should be selected (this is why there is no "select all" option!).
Downloaded library will be put into project folder like below
c. Library inclusion
We need to link the downloaded library to our view (blade file) :
- CSS part
<head>
<!-- DataTables -->
<link rel="stylesheet" href="{{ asset('/plugins/datatables/jquery.dataTables.css')}}">
</head>
- Javascript part
<!-- DataTables -->
<script src="{{ asset('/plugins/datatables/jquery.dataTables.min.js')}}"></script>
d. Plugin Initialization
Basically we will draw the wireframe of our table first using HTML like below
<div class="row">
<div class= "col-md-12">
<div class="box box-info">
<div class="box-header with-border">
<h3 class="box-title">Users List'</h3>
</div>
<!-- /.box-header -->
<div class="box-body">
<div class="table-responsive">
<table class="display" cellspacing="0" width="100%" id = "dataTable" name ="dataTable">
<thead>
<tr>
<th>User Name</th>
<th>Email</th>
<th>DOB</th>
<th>UserID</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<!-- /.table-responsive -->
</div>
<!-- /.box-body -->
</div>
</div>
</div>
Note that we left the <tbody> part to be blank and data will be filled later by serverside. The last column named UserID will be added to demonstrate the ability of having hidden column. What we are going to do is to load the user's list which has specified Group ID (input text group_id ) . Plugin's initialization will be like below:
var dataTable = $('#dataTable').DataTable(
{
"paging": true, // Allow data to be paged
"lengthChange": false,
"searching": true, // Search box and search function will be actived
"ordering": true,
"info": true,
"autoWidth": true,
"processing": true, // Show processing
"serverSide": true, // Server side processing
"deferLoading": 0, // In this case we want the table load on request so initial automatical load is not desired
"pageLength": 5, // 5 rows per page
"ajax":{
url : '',
type : "POST",
dataType: 'json',
error: function(data){
console.log(data);
}
},
// aoColumnDefs allow us to specify which column we want to make
// sortable or column's alignment
"aoColumnDefs": [
{ 'bSortable': false, 'aTargets': [0,1] } ,
{ className: "dt-center", "aTargets": [0,1,2,3] },
],
"columns": [
null,
null,
null,
{ "visible": false }, //The last column will be invisible
],
});
$('#button').on('click', function () {
var resourceURL = "{{route('user.datatables',['group_id'=>':group_id'])}}";
var group_id = 1; //Default group id
group_id = $('#group_id').val(); //Get the value of input text
resourceURL = resourceURL.replace(":group_id", group_id); // Build the route
/*
* Change the URL of dataTable and call ajax to load new data
*/
dataTable.ajax.url(targetUrl).load();
dataTable.draw();
} );
Note the technique that we used replace function to build the route dynamically.
III. Route definition
Route::post('user/{group_id}/datatables', ['as' => 'user.datatables','uses'=>'UserController@usersByGroupDatatables']);
The route we used as datatables's ajax url is defined above telling that function usersByGroupDatatables of UserController will be called to fetch datatable.
VI. Serverside logic
In this part, we will write logic code to handle fetching data from serverside:
public function usersByGroupDatatables(Request $request, $type, $group_id){
// The columns variable is used for sorting
$columns = array (
// datatable column index => database column name
0 =>'user_name',
1 =>'email',
2 =>'dob',
3 =>'id',
);
//Getting the data
$users = DB::table ( 'users' )
->where('users.group_id','=',$group_id)
->select ( 'users.id',
'users.user_name',
'users.email',
'users.dob',
);
$totalData = $users->count (); //Total record
$totalFiltered = $totalData; // No filter at first so we can assign like this
// Here are the parameters sent from client for paging
$start = $request->input ( 'start' ); // Skip first start records
$length = $request->input ( 'length' ); // Get length record from start
/*
* Where Clause
*/
if ($request->has ( 'search' )) {
if ($request->input ( 'search.value' ) != '') {
$searchTerm = $request->input ( 'search.value' );
/*
* Seach clause : we only allow to search on user_name field
*/
$candidates->where ( 'users.user_name', 'Like', '%' . $searchTerm . '%' );
}
}
/*
* Order By
*/
if ($request->has ( 'order' )) {
if ($request->input ( 'order.0.column' ) != '') {
$orderColumn = $request->input ( 'order.0.column' );
$orderDirection = $request->input ( 'order.0.dir' );
$jobs->orderBy ( $columns [intval ( $orderColumn )], $orderDirection );
}
}
// Get the real count after being filtered by Where Clause
$totalFiltered = $users->count ();
// Data to client
$jobs = $users->skip ( $start )->take ( $length );
/*
* Execute the query
*/
$users = $users->get ();
/*
* We built the structure required by BootStrap datatables
*/
$data = array ();
foreach ( $users as $user ) {
$nestedData = array ();
$nestedData [0] = $user->user_name;
$nestedData [1] = $job->email;
$nestedData [2] = $job->dob;
$nestedData [3] = $job->id;
$data [] = $nestedData;
}
/*
* This below structure is required by Datatables
*/
$tableContent = array (
"draw" => intval ( $request->input ( 'draw' ) ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval ( $totalData ), // total number of records
"recordsFiltered" => intval ( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data
);
return $tableContent;
}
V. Conclusion
We created an example of using Bootstrap datatables with most used functions above. The css of each cell can be changed on serverside by specifying directly in nestData[]. For example :
$nestedData [1] = '<small class="label bg-' . $user->display . '">' . $user->email . '</small>';
This will display email in label format coresponding display option used for email ( bg-green for example).
This is a very simple example but might help who want to work with Boostrap table at first.