Incorrect parameters in the call to native function 'LOWER' on search

[lukecurtis93]

Summary of problem or feature request

I have the following code for a table, a Customer object with a Belongs to Parent which is a parent_id column that references another customer model. I also have a full_address attribute with concatenates the address_line_one and address_line_two columns on my customer model

$( function() {
    $('.apiCustomerTable').DataTable({
        serverSide: true,
        processing: true,
        ajax:{
            "url": '/api/datatables/customers',
            "type": "POST"
        },
        "rowCallback": function( row, data, dataIndex ) {
           $(row).addClass( 'clickable-row' );
           $(row).attr("data-href", '/admin/customers/'+data["url_id"]+'/edit');
           $(document).on('click', '.clickable-row', function() {
                window.location = $(this).data("href");
            });
        },
        columns: [
            {data: 'first_name'},
            {data: 'parent', name:'parent.first_name'},
            {data: 'address', name: 'address_line_one'},
            {data: 'email'},
            {data: 'mobile'},
        ]
    });
});

In my controller:

return datatables()->of(Customer::with('parent')->select('customers.*'))
        ->editColumn('first_name', function ($customer) {
            return $customer->name;
        })->addColumn('url_id', function ($customer) {
            return $customer->id;
        })->addColumn('parent', function($customer){
            return $customer->parent ? $customer->parent->name : '';
        })->editColumn('address', function($customer){
            return $customer->full_address;
        })
        ->toJson();

But I receive the following error when searching:

Incorrect parameters in the call to native function 'LOWER'

Incorrect parameters in the call to native function 'lower' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `customers` where (LOWER(`customers`.`first_name`) LIKE %admin% or exists (select * from `customers` as `laravel_reserved_0` where `laravel_reserved_0`.`id` = `customers`.`parent_id` and LOWER(`customers` as `laravel_reserved_0.first_name`) LIKE %admin% and `laravel_reserved_0`.`deleted_at` is null) or LOWER(`customers`.`address_line_one`) LIKE %admin% or LOWER(`customers`.`email`) LIKE %admin% or LOWER(`customers`.`mobile`) LIKE %admin%) and `customers`.`deleted_at` is null) count_row_table)"}

System details

• Operating System - Homestead, Ubuntu
• PHP Version - 7.2
• Laravel Version - 5.7.11
• Laravel-Datatables Version "name": "yajra/laravel-datatables-oracle", "version": "v8.9.1",

[yajra]

I think you are using self join on the eager loaded relation right? Unfortunately, self join is currently not supported yet.

The issue on the generated SQL is on LOWER(`customers` as `laravel_reserved_0.first_name`). If you can fix, please do not hesitate to submit a PR. thanks!

[mkalnin]

I had the similar problem and looks like found the solution, here is the code example
//Controller
$users = User::select('users.*',
'users.company_name as holdingCompany')
->leftJoin('users as holdingCompany', 'holdingCompany.id','=','users.user_id');
$users->with('holding');
$dt = DataTables::of($users);
$dt->addColumn('holding', function ($users) {
if ($users->holding) {
$holding = $users->holding->company_name;
return $holding;
} else {
return '';
}
});
return $dt->rawColumns(['holding'])->make(true);

//JS
$('#customersTable').DataTable({
processing: true,
serverSide: true,
ajax: '/admin/users/getdata',
columns: [{
data: 'id',
name: 'id'
},
{
data: 'company_name',
name: 'company_name'
},
{
data: 'first_name',
name: 'first_name'
},
{
data: 'last_name',
name: 'last_name'
},
{
data: 'holding',
name: 'holdingCompany.company_name'
}
]
});

//Model
class User extends Authenticatable
{
public function holding()
{
return $this->belongsTo('App\User', 'user_id');
}
}

[yajra]

fixed in v12, thanks!