How to add buttons and filters to ajax datatable (yajra)
titoshadow opened this issue · 7 comments
Hi again !
I've observed there is a huge impact on dataTables drawing time when retrieving thousands of rows from database.
I have to bring them all because of I need the whole rows dataset to be searchable and sortable trough the dataTable tools, and tested some ways (cache) to make it faster, but improvements are quite little compared to drawing time.
Do you have some idea? Once improved, I would like to add filters to allow date filter / search, and so on.
Okey, I'm following a path that looks promising.
I'm trying to use server side processing on datatable, thus being able to send a JSON with my answer from controller to datatable, getting a much faster page loading.
To achieve this, my "index" on Controller looks like this:
public function index(Request $request)
{
$items = cache()->remember('usersList', now()->addMinutes(5), function () {
return Contact::latest('updated_at')->get();
});
if ($request->ajax()) {
return Datatables::of($items)
->make(true);
}
}
And this JS:
processing: true,
serverSide: true,
deferRender: true,
ajax: {
url: "http://10.10.10.112/admin/contacts",
type: 'GET'
},
columns: [
{
data: 'name',
name: 'name'
},
{
data: 'phone',
name: 'phone'
},
{
data: 'address',
name: 'addresss',
},
{
data: 'email',
name: 'email'
},
{
data: 'zip_code',
name: 'zip_code',
},
{
data: 'charge',
name: 'charge'
},
{
data: 'company',
name: 'company'
},
{
data: 'actions',
name: 'actions',
}
]
})
}).columns([2, 4]).visible(false);
My doubt:
How to add the edit, delete, etc buttons to final datatable (either sending HTML from controller, or loading it within ajax call) ?
Since I cannot insert the proper HTML (which contains "{{ route('blabla') }} expressions) or dunno how to escape them.
Not need for cache, because the problem is not in server, but in frontend(Js), I suggest you to use https://github.com/yajra/laravel-datatables
Thanks for answering !
I'm actually using it, but I'm not able to put "Edit" or "Delete" buttons for each row, not sure how to achieve it.
I tried passing the HTML from the controller as part of the ajax response, with no avail because of escaping special chars in smthing like this:
if ($request->ajax()) {
return Datatables::of($items)
->addColumn('actions', function ($row) {
return '
<td>
<ul class="list-inline">
<li class="list-inline-item">
<a href="{!! route(ADMIN . \'.contacts.edit\', $item->id) !!}"
title="{!! __(\'app.edit_title\') !!}" class="btn btn-primary btn-sm">
<span
class="fa fa-pencil" aria-hidden="true">
</span>
</a>
</li>
<li class="list-inline-item">
{{ Form::open([
\'class\'=>\'delete\',
\'url\' => route(ADMIN . \'.contacts.destroy\', $item->id),
\'method\' => \'DELETE\',
])
}}
<button class="btn btn-danger btn-sm" title="{{ __(\'app.delete_title\') }}"><i
class="fa fa-trash"></i></button>
{!! Form::close() !!}
</li>
</ul>
</td>';
Ok, regarding the row "edit" and "delete" buttons, I have ended having a "buttons" variable defined in form.blade.php (so I'm able to use blade {{ }} syntax), then using "render" in actions column, this way:
form.blade.php:
<script>
let buttons = '<td> ' +
'<ul class="list-inline">' +
' <li class="list-inline-item">' +
' <a href="{!! route(ADMIN . '.contacts.edit', $item->id) !!}"' +
' title="{!! __('app.edit_title') !!}" class="btn btn-primary btn-sm">' +
' <span class="fa fa-pencil" aria-hidden="true">' +
' </span>' +
' </a>' +
' </li>' +
' <li class="list-inline-item">' +
' {{ Form::open(['class'=>'delete', 'url' => route(ADMIN . '.contacts.destroy', $item->id), 'method' => 'DELETE']) }}' +
' <button class="btn btn-danger btn-sm" title="{{ __('app.delete_title') }}"><i' +
' class="fa fa-trash"></i></button>' +
' {!! Form::close() !!}' +
' </li>' +
' </ul>' +
' </td>';
</script>
index.js:
$('.contacts-table').DataTable({
language: {
processing: "<i class='fa fa-refresh fa-spin' style='margin-left: 30%'></i><small> Loading ...</small>"
},
processing: true,
serverSide: true,
deferRender: true,
ajax: {
url: "http://10.10.10.112/admin/contacts",
type: 'GET'
},
columns: [
{
data: 'name',
name: 'name'
},
{
data: 'phone',
name: 'phone'
},
{
data: 'address',
name: 'address',
},
{
data: 'email',
name: 'email'
},
{
data: 'zip_code',
name: 'zip_code',
},
{
data: 'charge',
name: 'charge'
},
{
data: 'company',
name: 'company'
},
{
name: 'actions',
render: function(o){
return buttons; // Written in form.blade.php
}
}
]
})
Obviusly I need to rearrange the code in a better way (ideas are welcome) to be more elegant.
However, I've been completely uncapable to add DataTables export buttons, as per specified in https://laravel-datatables-docs.netlify.com/#/buttons-starter
Where and how, regarding your JS code organization, should I implement them?
Closed because of mixing too issues in one thread ..
Currently, my only problem is:
I've been completely uncapable to add DataTables export buttons, as per specified in https://laravel-datatables-docs.netlify.com/#/buttons-starter
Where and how, regarding your JS code organization, should I implement them?
I add export using :
// Data table
$('.datatable').DataTable({
language: {
// 'url': '/plugins/i18n/dataTables-french.json'
'url' : 'https://cdn.datatables.net/plug-ins/1.10.16/i18n/French.json'
// More languages : http://www.datatables.net/plug-ins/i18n/
},
aaSorting: [],
dom: 'Bfrtip',
buttons: [
// 'copy', 'csv', 'excel', 'pdf', 'print'
{ extend: 'copy', text: 'copie' },
{ extend: 'csv', text: 'csv' },
{ extend: 'excel', text: 'excel' },
{ extend: 'pdf', text: 'pdf' },
{
extend: 'print',
text: 'impression',
exportOptions: {
columns: [':visible :not(.hidden-print)'],
}
},
]
});
Result :
This is my complete datatable declaration (with your addition)
let contacts_table = $('.contacts-table').DataTable({
language: {
processing: "<i class='fa fa-refresh fa-spin' style='margin-left: 30%'></i><small> Loading ...</small>"
},
processing: true,
stateSave: true,
serverSide: true,
deferRender: true,
dom: 'Bfrtip',
buttons: [
// 'copy', 'csv', 'excel', 'pdf', 'print'
{ extend: 'copy', text: 'copie' },
{ extend: 'csv', text: 'csv' },
{ extend: 'excel', text: 'excel' },
{ extend: 'pdf', text: 'pdf' },
{
extend: 'print',
text: 'impression',
exportOptions: {
columns: [':visible :not(.hidden-print)'],
}
},
],
columnDefs: [
{"orderData": [], "targets": 0}
],
pagingType: 'numbers',
ajax: {
url: "contacts",
type: 'GET'
},
columns: [
{
data: 'name',
name: 'name'
},
{
data: 'phone',
name: 'phone'
},
{
data: 'address',
name: 'address'
},
{
data: 'email',
name: 'email'
},
{
data: 'zip_code',
name: 'zip_code'
},
{
data: 'charge',
name: 'charge'
},
{
data: 'company',
name: 'company'
},
{
name: 'actions',
render: function (o) {
return actionButtons; // Written in form.blade.php
},
searchable: false,
orderable: false
}
]
});
contacts_table.columns([2, 4]).visible(false);
contacts_table.columns.adjust().draw(false);
I obtaing nothing :(
When using "dom": "Bfrtip", I just lose the page length control.
Since I was unable to make it work, I just added a new "Exporting" blade, with filters and options.
Thanks for your help.