laravel datatables search and sort not working on custom sql query

have laravel query like this:

$clients = DB::table('clients') 
    -> select(['clients.id', 'client_groups.name as group', 'clients.updated_at', 'clients.company_name', 'countries.name as country', 'clients.firstname', 'clients.lastname', 'clients.office_phone', 'clients.mob_phone'])
    -> leftJoin('client_groups', 'clients.group_id', '=', 'client_groups.id')
    -> leftJoin('countries', 'clients.country_id', '=', 'countries.id')
    -> whereNull('clients.deleted_at')
    -> where('clients.user_id', $user_id);

and it returns all good data..

also trying to use datatables like this:

Datatables::of($clients)
-> addColumn('group', function($client){
    return $client -> group;
}) -> filterColumn('group', function($query, $keyword) {
    $query->whereRaw("group like ?", ["%{$keyword}%"]);
})

-> addColumn('last_contact', function($client){
    return $client -> updated_at;
}) -> filterColumn('last_contact', function($query, $keyword) {
        $query->whereRaw("updated_at like ?", ["%{$keyword}%"]);
})

-> addColumn('company', function($client){
    return $client -> company_name;
}) -> filterColumn('company', function($query, $keyword) {
        $query->whereRaw("company_name like ?", ["%{$keyword}%"]);
})

-> addColumn('country', function($client){
    return $client -> country;
}) -> filterColumn('country', function($query, $keyword) {
        $query->whereRaw("country like ?", ["%{$keyword}%"]);
})

-> addColumn('name', function($client){
    return $client -> firstname.' '.$client -> lastname;
}) -> filterColumn('name', function($query, $keyword) {
        $query->whereRaw("CONCAT(firstname,' ',lastname) like ?", ["%{$keyword}%"]);
})

-> addColumn('phone', function($client){
    return $client -> office_phone.' '.$client -> mob_phone;
}) -> filterColumn('phone', function($query, $keyword) {
        $query->whereRaw("CONCAT(office_phone,' ',mob_phone) like ?", ["%{$keyword}%"]);
})


-> addColumn('action', function ($client) {
    $output = '';
    if(Module::hasAccess("Clients", "edit")) {
        $output .= '<a href="'.url(config('laraadmin.adminRoute') . '/clients/'.$client -> id.'/edit').'" class="btn btn-warning btn-xs" style="display:inline;padding:2px 5px 3px 5px;"><i class="fa fa-edit"></i></a>';
    }

    if(Module::hasAccess("Clients", "delete")) {
        $output .= Form::open(['route' => [config('laraadmin.adminRoute') . '.clients.destroy', $client -> id], 'method' => 'delete', 'style'=>'display:inline']);
        $output .= ' <button class="btn btn-danger btn-xs" type="submit"><i class="fa fa-times"></i></button>';
        $output .= Form::close();
    }
    return (string)$output;
})
-> make(true);

and at first it seems like everything is ok.. but when trying to start datatable search or sort in my view it throws errors like this:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group like ?) or (updated_at like ?) or (company_name like ?) or (country like ?' at line 1 (SQL: select count(*) as aggregate from (select '1' as row_count from clients left join client_groups on clients.group_id = client_groups.id left join countries on clients.country_id = countries.id where clients.deleted_at is null and clients.user_id = 1 and ((group like %di%) or (updated_at like %di%) or (company_name like %di%) or (country like %di%) or (CONCAT(firstname,' ',lastname) like %di%) or (CONCAT(office_phone,' ',mob_phone) like %di%) or clients.action LIKE %di%)) count_row_table)

My datatables js code looks like this:

$("#example1").DataTable({
    processing: true,
    serverSide: true,
    ajax: "MY_URL",
    columns: [
        {data: 'group', name: 'group'},
        {data: 'last_contact', name: 'last_contact'},
        {data: 'company', name: 'company'},
        {data: 'country', name: 'country'},
        {data: 'name', name: 'name'},
        {data: 'phone', name: 'phone'},
        @if($show_actions)
            {data: 'action', name: 'action'}
        @endif
    ],
    @if($show_actions)
        columnDefs: [ { orderable: false, targets: [-1] }],
    @endif
});

is it possible to get working with datatables and sql queries like I showed at the top in correct way? how?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2k1iAHy
via IFTTT