You will most likely find a better more actively maintained fork here https://github.com/yajra/laravel-datatables
If you have issues please try and fix them and we will pull the changes if we can verify they work. That being said this project lacks automatic testing so it has become a difficult project to maintain. Please let us know if you are interested in adopting and maintaining this project, it is still pretty useful. 60% of the time, it works every time.
About
This bundle is created to handle the server-side processing of the DataTables Jquery Plugin (http://datatables.net) by using Eloquent ORM or Fluent Query Builder.
Require bllim/datatables
in composer.json and run composer update
.
{
"require": {
"laravel/framework": "4.0.*",
...
"bllim/datatables": "*"
}
...
}
Composer will download the package. After the package is downloaded, open app/config/app.php
and add the service provider and alias as below:
'providers' => array(
...
'Bllim\Datatables\DatatablesServiceProvider',
),
'aliases' => array(
...
'Datatables' => 'Bllim\Datatables\Facade\Datatables',
),
Finally you need to publish a configuration file by running the following Artisan command.
$ php artisan config:publish bllim/datatables
It is very simple to use this bundle. Just create your own fluent query object or eloquent object without getting results (that means don't use get(), all() or similar methods) and give it to Datatables. You are free to use all Eloquent ORM and Fluent Query Builder features.
Some things you should know:
select
method on Eloquent or Fluenty Query, you choose columns.edit_column($column, $content)
remove_column($column)
$content
values.add_column
or edit_column
calls. The function receives a single parameter: the query row/model record. (see Example 2)posts.id
the relevant identifier is id
, and for owner.name as ownername
it is ownername
set_index_column($name)
set_row_class($content)
function.set_row_data($name,$content)
function.make(true)
to return an array of objects instead of an array of arrays. (see Example 4)Example 1: Simple use
$posts = Post::select(array('posts.id','posts.name','posts.created_at','posts.status'));
return Datatables::of($posts)->make();
Example 2: Adding and editing columns
$place = Place::left_join('owner','places.author_id','=','owner.id')
->select(array('places.id','places.name','places.created_at','owner.name as ownername','places.status'));
return Datatables::of($place)
->add_column('operations', '<a href="https://github.com/bgultekin/laravel4-datatables-package/blob/master/{{ URL::route( \'admin.post\', array( \'edit\',$id )) }}">edit</a>
<a href="https://github.com/bgultekin/laravel4-datatables-package/blob/master/{{ URL::route( \'admin.post\', array( \'delete\',$id )) }}">delete</a>
')
->edit_column('status', '{{ $status ? 'Active' : 'Passive' }}')
->edit_column('ownername', function($row) {
return "The author of this post is {$row->ownername}";
})
->remove_column('id')
->make();
Notice: If you use double quotes while assigning the $content in an add_column
or edit_column
call, you should escape variables with a backslash (\) to prevent an error. For example:
edit_column('id', "{{ \$id }}") .
Example 3: Using filter_column
$clients = Client::select(array(
'Client.id',
DB::raw('CONCAT(Client.firstname," ",Client.lastname) as ClientName'),
'Client.email',
'Client.code',
'Client.updated_at',
'Client.isActive',
'Language.name as LanguageName',
))
->leftJoin('Language', 'Client.Language_id', '=', 'Language.id')
->where('isDeleted', '!=', '1');
return Datatables::of($clients)
->filter_column('id', 'where', 'Client.id', '=', '$1')
->filter_column('code', 'where', 'Client.code', '=', DB::raw('UPPER($1)'))
->filter_column('LanguageName', 'whereIn', 'Language.name', function($value) { return explode(',',$value); })
->filter_column('updated_at', 'whereBetween', 'Client.updated_at', function($value) { return explode(',',$value); }, 'and')
->edit_column('isActive', '@if($isActive) <span class="label label-success">Active</span> @else <span class="label label-danger">Inactive</span> @endif')
->make();
Notes on filter_column:
Usage: filter_column ( $column_name, $method, $param_1, $param_2, ..., $param_n )
$column_name
- the column name that search filter is be applied to$method
- can be any of QueryBuilder methods (where, whereIn, whereBetween, having etc.).
$param_1 ... $param_n
- these are parameters that will be passed to the selected where function ($method
). Possible types:
string
DB::raw()
- The DB::raw() can output literaly everything into the query. For example, subqueries or branching if you need some really sophisticated wheres.function
- or any other callablearray
of any of the above$1
placed anywhere in parameters. If a callable (function) is used the searched value is passed to callable as first parameter the first parameter (again see line 17).
Example 4: Returning an array of objects
$posts = Post::select(array('posts.id','posts.name','posts.created_at','posts.status'));
return Datatables::of($posts)->make(true);
This returns a JSON array with data like below:
data: {
{
id: 12,
name: 'Dummy Post',
created_at: '1974-06-20 13:09:51'
status: true
}
{
id: 15,
name: 'Test post please ignore',
created_at: '1974-06-20 13:15:51',
status: true
}
}
Example 5: DT_RowID, DT_RowClass and DT_RowData
$todo = ToDoList::select(array('todo.id','todo.name','todo.created_at','todo.status'));
return Datatables::of($todo)
->set_index_column('id')
->set_row_class('@if($status=="done") success @endif')
->set_row_data('created_at','{{$created_at}}')
->make();
Example 6: Advanced usage of dataFullSupport
To better utilize dataTables mData (1.9), now columns.data (1.10) feature you may enable dataFullSupport by either setting it to true in the config file, or passing true to the second initialization argument Datatables::of($query, true)
Creating a table with a searchable and sortable joined table:
//html
<table id="user-list"></table>
//script.js
$("#users-list").dataTable({
"processing": true,
"serverSide": true,
"ajax": "/api/user/datatables",
"order": [[1,'desc']],
"columnDefs": [ { //this prevents errors if the data is null
"targets": "_all",
"defaultContent": ""
} ],
"columns": [
//title will auto-generate th columns
{ "data" : "id", "title" : "Id", "orderable": true, "searchable": false },
{ "data" : "profile.last_name","title" : "Name", "orderable": true, "searchable": true },
{ "data" : "username", "title" : "Username", "orderable": true, "searchable": true },
{ "data" : "email", "title" : "Email", "orderable": true, "searchable": true },
{ "data" : "created_date", "title" : "Created", "orderable": true, "searchable": true },
]
});
$users = Models\User::select()->ModelJoin('profile');
return $dataTables = Datatables::of($users)
->filter_column('profile.last_name','where',\DB::raw('CONCAT(profile.last_name,\' \',profile.first_name)'),'LIKE','$1')
->filter_column('created_at','where','users.created_at','LIKE','$1')
//for the blade template only the array data results is provided, it is `extracted` into the template
->edit_column('profile.last_name', '{{ $profile["first_name"]." ".$profile["last_name"] }}')
->edit_column('created_at', function($result_obj) {
//in a callback, the Eloquent object is returned so carbon may be used
return $result_obj->created_at->format('d/m/Y - h:ia');
})
->add_column('manage', '<a href="https://github.com/bgultekin/laravel4-datatables-package/blob/master/user/edit/{{$id}}" >Edit</a>', 3)
->remove_column('profile.photo_id')
->set_index_column('row-{{ $id }}')
->make();
//helper scope method in base Model class
public function scopeModelJoin($query, $relation_name, $operator = '=', $type = 'left', $where = false) {
$relation = $this->$relation_name();
$table = $relation->getRelated()->getTable();
$one = $relation->getQualifiedParentKeyName();
$two = $relation->getForeignKey();
if (empty($query->columns)) {
$query->select($this->getTable().".*");
}
//$join_alias = $table;
$prefix = $query->getQuery()->getGrammar()->getTablePrefix();
$join_alias = $relation_name;
foreach (\Schema::getColumnListing($table) as $related_column) {
$query->addSelect(\DB::raw("`$prefix$join_alias`.`$related_column` AS `$join_alias.$related_column`"));
}
$two = str_replace($table . ".", $join_alias . ".", $two);
return $query->join("$table AS $prefix$relation_name", $one, $operator, $two, $type, $where); //->with($relation_name);
}
Notes on columns.data:
License: Licensed under the MIT License