Level 6
I figured it out. I was missing the true value for Manual Searching with Global Search
Adding true after the filter function re-enables the global search after the manual search.
1 like
Feb 23, 2022
2
Level 6
Laravel/Datatables: Custom filter interfering with standard search
This is going to be a long post, so please bear with me.
Using the Laravel Datatables Package I have a table of providers, with some complex rendering on the front-end. I have it set up currently so that everything that I want to be searchable is through the standard text-input, and it works fantastically. However, I also want to have dropdowns to mass filter on certain fields like "Status" and "Type". But when I implement the dropdown using the built-in filter() method on the Datatable, the search via text-input stops working and instead no matter what I type it returns all results just the same.
My functional code WITHOUT the custom filters is this:
TableController:
public function providersIndexTable(Request $request)
{
//$providers = Provider::withAllRelations();
$providers = Provider::with([
'primaryPhysicalAddress', 'metadata', 'primaryContact', 'quality', 'caretype', 'category', 'status'
]);
try {
return Datatables::of($providers)->addColumn('last_update', function ($provider) {
$updated_at = $provider->updated_at->format('d M, Y').' at '.$provider->updated_at->format('g:ia');
$updated_by = Activity::where('subject_type', 'App\Models\Provider')
->where('subject_id', $provider->id)
->latest('created_at')->first();
return [
'updated_at' => $updated_at,
'updated_by' => User::find($updated_by->causer_id)->pluck('name')->first(),
'updater_id' => $updated_by->causer_id,
];
})->addColumn('type_and_quality', function ($provider) {
return [
'quality' => $provider->quality->stars,
'type' => $provider->caretype->type,
];
})->make();
} catch (\Exception $e) {
return response()->json(500, ['error' => 'There was an error building the table']);
}
}
and the actual table / blade looks like this:
<table class="table table-striped datatable" id="providers-table">
<thead>
<tr>
<!-- HIDDEN COLUMNS -->
<th>Last Update</th>
<th>City</th>
<th>Street</th>
<!-- VISIBLE COLUMNS -->
<th style="width: 5%;">Expand</th>
<th style="width: 15%;">Business Name</th>
<th style="width: 10%;">EPICS ID</th>
<th style="width: 10%">Type and Quality</th>
<th style="width: 20%;">Address</th>
<th style="width: 20%;">Contact</th>
<th style="width: 15%">Last Update</th>
<th style="width: 5%;">Actions</th>
<!-- VISIBLE COLUMNS -->
</tr>
</thead>
<tbody>
</tbody>
</table>
<script>
$(document).ready(function () {
var table = $('#providers-table').DataTable({
createdRow: function (row, data) {
$(row).addClass(data.status.color);
},
dom: tableHeader + tableButtons + tableBody + tableFooter,
buttons: [
'excel',
'print'
],
lengthMenu: [[10, 15, 25, 50, 100, -1], [10, 15, 25, 50, 100, "All"]],
processing: true,
language: {
loadingRecords: ' ',
processing: function () {
var loader = $('#providers-table_processing');
var width = $('.card-table').width();
loader.width(width);
return loadingMessage;
},
},
serverSide: true,
ajax: '{{ route('tables.providers.index') }}',
columns: [
{
data: 'updated_at',
name: 'updated_at',
defaultContent: "",
searchable: false,
orderable: true,
visible: false,
},
{
data: 'primary_physical_address.city',
name: 'primaryPhysicalAddress.city',
defaultContent: "",
searchable: true,
visible: false,
},
{
data: 'primary_physical_address.street_address',
name: 'primaryPhysicalAddress.street_address',
defaultContent: "",
searchable: true,
visible: false,
},
{
name: 'details-control',
className: 'details-control',
orderable: false,
searchable: false,
data: null,
defaultContent: '',
searchable: false,
render: function () {
return '<div class="icon text-primary text-center" type="button"></span><br>\
<span class="mdi mdi-hc-lg mdi-plus-circle" ></span>\
</div>';
},
},
{
data: 'business_name',
name: 'business_name',
defaultContent: "",
searchable: true,
orderable: true,
render: function (data, type, row) {
var name = row.business_name;
if ((!name || /^\s*$/.test(name))) {
name = '<span class="badge badge-warning mb-1">No Business Name</span>';
} else {
name = '<span>' + row.business_name + '</span>';
}
return name + '<br>\
<span class="badge badge-info">Tag 1</span> <span class="badge badge-info">Tag 2</span>';
},
},
{
data: 'metadata.epics_id',
name: 'metadata.epics_id',
defaultContent: "",
searchable: true,
orderable: true,
render: function (data) {
if (data == null) {
return '<span class="badge badge-warning">No Data / NA</span>';
} else {
return '<span class="badge badge-secondary">' + data + '</span>';
}
}
},
{
data: 'type_and_quality',
name: 'type_and_quality',
defaultContent: "",
searchable: false,
orderable: false,
render: function (data, type, row) {
let stars = '';
let level = data.quality;
if (level === 1) {
stars = '<span class="text-dark h4">\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
</span>';
}
if (level === 2) {
stars = '<span class="text-dark h4">\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
</span>';
}
if (level === 3) {
stars = '<span class="text-dark h4">\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star-outline"></i>\
<i class="icon mdi mdi-star-outline"></i>\
</span>';
}
if (level === 4) {
stars = '<span class="text-dark h4">\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star-outline"></i>\
</span>';
}
if (level === 5) {
stars = '<span class="text-dark h4">\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
<i class="icon mdi mdi-star"></i>\
</span>';
}
return '<span class="badge badge-secondary mb-1">' + data.type + '</span><br>' + stars;
},
},
{
data: 'primary_physical_address',
name: 'primaryPhysicalAddress',
defaultContent: "No Data",
searchable: false,
orderable: false,
render: function (data, type, row) {
if (row.primary_physical_address == null) {
return '<span class="badge badge-secondary">No Data</span>';
} else {
return row.primary_physical_address.street_address +
'<br>' + row.primary_physical_address.city + ', ' +
row.primary_physical_address.state +
'<br>' + row.primary_physical_address.zip;
}
},
},
{
data: 'primary_contact',
name: 'primaryContact',
defaultContent: "No Data",
searchable: false,
orderable: false,
render: function (data, type, row) {
if (row.primary_contact == null) {
return '<span class="badge badge-secondary">No Data</span>';
} else {
var email = row.primary_contact.email;
if (email === "" || email === null) {
var emailSpan = '<span class="badge badge-warning mb-1">No Email</span>';
} else {
var emailSpan = '<span class="badge badge-secondary mb-1">\
<a href="mailto:' + email + '">' + email + '</a></span>';
}
var phone = row.primary_contact.phone;
if (phone === "" || phone === null) {
var phoneSpan = '<span class="badge badge-warning mb-1">No Phone</span>';
} else {
var phoneSpan = '<span class="badge badge-secondary mb-1">' + phone + '</span>';
}
return emailSpan + '<br>' + phoneSpan;
}
},
},
{
data: 'last_update',
name: 'last_update',
className: 'cell-detail',
defaultContent: "No Data",
searchable: false,
orderData: [0],
render: function (data) {
var user = '{{ route('users_show', ':id') }}';
var user_url = user.replace(':id', data.updater_id);
return '<span>' + data.updated_at + '</span>\
<span class="cell-detail-description">by <a href="' + user_url + '">' + data.updated_by + '</a></span>';
},
},
{
data: 'id',
name: 'id',
className: 'text-right',
orderable: false,
searchable: false,
render: function (data) {
var show = '{{ route('providers.show', ':id') }}';
var show_url = show.replace(':id', data);
var edit = '{{ route('providers.edit', ':id') }}';
var edit_url = edit.replace(':id', data);
return '<div class="btn-group">\
<a href="' + show_url + '" target="_blank" class="btn btn-secondary" data-toggle="tooltip" data-placement="left" title="View Provider Details">\
<i class="icon mdi mdi-info"></i>\
</a>\
<a href="' + edit_url + '" class="btn btn-secondary" type="button" data-toggle="tooltip" data-placement="left" title="Edit Provider">\
<i class="icon mdi mdi-edit"></i>\
</a>\
</div>';
},
}
],
order: [[4, 'asc']],
drawCallback: function (settings) {
$('[data-toggle="tooltip"]').tooltip();
},
});
$("div.dataTables_wrapper").css({
'width': ($("#providers-table").width() + 'px')
});
});
</script>
Sorry for the long paste/code-dump.
When I try to implement the custom filters, I have dropdowns above the table, and I change the ajax in the javascript to be this:
ajax: {
url: '{{ route('tables.providers.index') }}',
data: function (d) {
d.quality = $('#quality').val(),
d.status_id = $('#status_id').val(),
d.caretype = $('#caretype').val(),
d.search = $('input[type="search"]').val()
},
},
and I add this to the controller after the last addColumn and before the call to ->make()
->filter(function ($provider) use ($request) {
if ($request->get('quality')) {
$provider->where('quality_id', $request->get('quality'));
}
if ($request->get('status_id')) {
$provider->where('status_id', $request->get('status_id'));
}
if ($request->get('caretype')) {
$provider->where('type_of_care_id', $request->get('caretype'));
}
})->rawColumns(['quality_id', 'status_id'])
I know that I need to handle the input from the search text field, but I'm not sure what to put there so that the datatable filters as it does automatically.
So my question is this: How can I make it so that any input into the search text box will filter / query the DB the same way it would as if I didn't have the additional filter() field on the Datatable in the controller? When I do NOT include the filter, the search works flawlessly, and when I do include the filter, the filters (dropdowns / selects) work but the text-search does not.
Level 3
@garrettmassey Damn, I really learnt a lot from reading this code. Is there like a tutorial or any material that will enable me to better understand datatables?
Please or to participate in this conversation.