Description
This is a web page made using PHP Laravel that uses Yajra Data Table with ServeSide on. it has 5 dropdowns filters and global search when a filter is selected Ajax Call is used and DataTable is re initialized with filtered data array.
Issue: that i am facing is that i am using column search functionality,Column which has special chracter as value saved in database, is not being searched correctly.
Column Search Test Case-1
column name: Gender
search Value: male
results: OK
Column Search Test Case-2
column name: Segment
search Value: Admin
Column Value: ADMIN/BACKOFFICE
results: No Results
Column Search Test Case-3
column name: Type
Column Value: FB-ENDORSED
search Value: fb
results: No Results
Becasue Segment & Type Column in my database has value ADMIN/BACKOFFICE & FB-ENDORSED respectively. becasue of / & - , i am unable to search from these column.
Below are my front-end and back-end code
FRONT-END
//start yajra table load
function load_datatable() {
option_table = $('#smTable').DataTable({
ordering: true,
serverSide: true,
ajax: {
url: "{{ route('view-smart-search-table') }}",
type: "GET",
},
drawCallback: function(settings) {
$('.hidetrIDSmartSearch').find('tr').each(function() {
$(this).click(function() {
window.open($(this).attr('data-href'), '_blank');
});
});
},
createdRow: function(row, data, dataIndex) {
$(row).addClass('id');
let id = $(row).find('td:first').text().trim();
$(row).attr('data-href', `{{ url('admin/details/${id}') }}`);
},
initComplete: function(settings, json) {
// Apply the search
$('#foundRecord').val(json.recordsTotal)
$('#smTable').on('draw.dt', function() {
if (settings.oFeatures.bServerSide === true) {
summaryAppendAjax(settings.json.array);
}
});
$("#loader").hide();
$('#smTable_length').hide();
this.api().columns().every(function() {
var column = this;
var input = $(
'<input type="text" class="form-control form-control-sm" placeholder="Search...">'
)
.appendTo($(column.footer()).empty())
.attr('data-column', column.data()) // Add custom attribute
.on('keyup change clear', function() {
if (column.search() !== this.value) {
column.search(this.value).draw();
}
});
});
},
columns: [{
data: 'id',
name: 'id',
ordering: true, // Enable sorting
searchable: false
},
{
data: 'DT_RowIndex',
name: 'DT_RowIndex',
searchable: false,
}
//Other All columns goes here...
],
dom: 'Blfrtip',
columnDefs: [{
targets: 1,
className: 'noVis'
}],
bInfo: true,
buttons: [{
extend: 'colvis',
collectionLayout: 'fixed two-column',
text: 'List of Visible Coloumn Names in Current Table(Click to Deselect a Coloumn)',
columns: ':not(.noVis)'
}]
});
}
BACKEND-CODE
// convert table to yajra data table on page load
public function smartTOYajra(Request $request)
{
$columns = $request->input('columns');
// Initialize the new array
$newArray = [];
// Iterate through the $columns array using a for loop
for ($i = 0; $i < count($columns); $i++) {
// Check if the condition is met
if (!empty($columns[$i]['search']['value'])) {
// Push the value into the new array
$newArray[] = $columns[$i]['search']['value'];
}
}
$record = DB::table('updated_view_record');
// Check if the new array is empty or has values
if (empty($newArray)) {
// Global search is being perfomed here
if ($request->search['value'] != '') {
$columnArray = ['team_name', 'recruiter_name', 'first_name', 'middle_name', 'last_name' ]; // and remaning columns
// perfomr query
foreach ($columnArray as $value) {
$search = "%" . $request->search['value'] . "%";
$record->orWhere($value, 'like', "'$search'");
}
// get data array after global search
$sqlQuery = Str::replaceArray('?', $record->getBindings(), $record->toSql());
$result = DB::select($sqlQuery);
$record = $result;
} else {
}
} else {
// columns search query is being performed here
for ($i = 0; $i < count($columns); $i++) {
if ($columns[$i]['search']['value'] != '') {
$searchValue = $columns[$i]['search']['value'];
$record->where($columns[$i]['name'], 'LIKE', "%$searchValue%");
}
}
}
return Datatables::of($record)
->addIndexColumn()
->addColumn('id', function ($record) {
return $record->cid . '-' . $record->endorsement_id . '-' . $record->saved_by . '-' . $record->finance_id;
})
->addColumn('team_name', function ($record) {
return $record->team_name;
})
->addColumn('recruiter_name', function ($record) {
return $record->recruiter_name;
})
//remaining columns goes here
->setTotalRecords($totalCount)
->with([
'array' => $this->candidate_arr,
'appendSummary' => 1,
'totalCount' => $totalCount,
])
->rawColumns([
'id',
'DT_RowIndex',
'team_name',
//remaining columns goes here
])
->make(true);
ANY KIND OF HELP WILL BE APPRICIATED.
i ahve developed page using yajra data table, but i am facing issue in column searching of those columns which have special characters in their values. I want my search to be work partially perfect for those columns which have special character like (-, /) like 're-type'.