Hello i create datatables serverside with PHP, but filtering result is wrong.
i want to show only 1 data, when i filter “epic 5” but the result show 5 data.
You can check my screenshot here : https://i.ibb.co.com/4RnRcwLg/Screenshot-1.png
When i debug SQL code show like this. Why datatables split my filtering?
SELECT
`rank_star`.*,
`rk`.`rank_name`,
`usr`.`employee_username`
FROM
`rank_star`
LEFT JOIN `rank` AS `rk` ON `rk`.`id_rank` = `rank_star`.`id_rank`
LEFT JOIN `employee` AS `usr` ON `usr`.`employee_id` = `rank_star`.`created_by`
LEFT JOIN `employee` AS `usr_up` ON `usr_up`.`employee_id` = `rank_star`.`updated_by`
WHERE
(
LOWER( `rk`.`rank_name` ) LIKE % epic %
OR LOWER( `rank_star`.`rank_name_star` ) LIKE % epic %
OR LOWER( `rank_star`.`rank_star_order` ) LIKE % epic %
OR LOWER( `rank_star`.`created_at` ) LIKE % epic %
OR LOWER( `rank_star`.`updated_at` ) LIKE % epic %
OR LOWER( `usr`.`employee_username` ) LIKE % epic %
OR LOWER( `usr_up`.`employee_username` ) LIKE % epic %)
AND (
LOWER( `rk`.`rank_name` ) LIKE % 5 %
OR LOWER( `rank_star`.`rank_name_star` ) LIKE % 5 %
OR LOWER( `rank_star`.`rank_star_order` ) LIKE % 5 %
OR LOWER( `rank_star`.`created_at` ) LIKE % 5 %
OR LOWER( `rank_star`.`updated_at` ) LIKE % 5 %
OR LOWER( `usr`.`employee_username` ) LIKE % 5 %
OR LOWER( `usr_up`.`employee_username` ) LIKE % 5 %)
ORDER BY
`rank_star`.`rank_star_order` ASC
LIMIT 100 OFFSET 0
This is my javasript code
$(document).ready(function(){
$("#example").DataTable({
search: { "bSmart": false, "bRegex": true },
serverSide : true,
processing : true,
ajax : {
url : "rank_star/res",
type : 'POST',
dataType : 'JSON',
data : {_token : '{{csrf_token()}}'}
},
columns : [
{data : 'act', name : 'act'},
{data : 'rank_name', name : 'rk.rank_name'},
{data : 'rank_name_star', name : 'rank_star.rank_name_star'},
{data : 'rank_min_star', name : 'rank_min_star',className: "text-center"},
{data : 'rank_max_star', name : 'rank_max_star',className: "text-center"},
{data : 'rank_star_order', name : 'rank_star.rank_star_order',className: "text-center"},
{data : 'created_date', name : 'created_at'},
{data : 'updated_date', name : 'updated_at'},
{data : 'employee_add', name : 'usr.employee_username'},
{data : 'employee_update', name : 'usr_up.employee_username'},
],
pageLength: 100,
order: [[5, 'asc']],
});
});
And this is my PHP Code
$data = RankStar::query()->select(
[
'rank_star.*',
'rk.rank_name',
'usr.employee_username',
]
)
->leftJoin('rank as rk', 'rk.id_rank', '=', 'rank_star.id_rank')
->leftJoin('employee as usr', 'usr.employee_id', '=', 'rank_star.created_by')
->leftJoin('employee as usr_up', 'usr_up.employee_id', '=', 'rank_star.updated_by');
return datatables()->eloquent($data)
->addColumn("act", function($row){
$func_action ="";
$btn_edit = "<button type='button' title='Update' class='btn btn-primary btn-sm' onclick='do_edit("".$row->id_rank_star."")' data-toggle='modal' data-target='#update_modal'><i class='fa fa-pencil'></i></button> ";
$btn_delete = "<button type='button' title='Delete' class='btn btn-danger btn-sm' onclick='do_delete("".$row->id_rank_star."")'><i class='fa fa-trash'></i></button>";
if(acc_update(Session::get('ses_level'),$this->data['id_menu']) == '1'){
$func_action .= $btn_edit;
}
if(acc_delete(Session::get('ses_level'),$this->data['id_menu']) == '1'){
$func_action .= $btn_delete;
}
return "<center>".$func_action."</center>";
})
->addColumn("rank_name", function($row){
return @$row->rank_name;
})
->addColumn("rank_name_star", function($row){
return @$row->rank_name_star;
})
->addColumn("rank_min_star", function($row){
return @$row->rank_min_star;
})
->addColumn("rank_max_star", function($row){
return @$row->rank_max_star;
})
->addColumn("rank_star_order", function($row){
return @$row->rank_star_order;
})
->addColumn("employee_add", function($row){
return @$row->creator->employee_username;
})
->addColumn("employee_update", function($row){
return @$row->updater->employee_username;
})
->addColumn("created_date", function($row){
return get_date_indonesia($row->created_at)." ".substr($row->created_at, 10, 9);
})
->addColumn("updated_date", function($row){
return get_date_indonesia($row->updated_at)." ".substr($row->updated_at, 10, 9);
})
->rawColumns(['act'])
->make(true);
Help me sir, thanks.