Laravel-datatables: [6.0] Search error when using column alias.

[Dewilas]

Hi, I have problem with sql query in v6.28.0 version. Laravel 5.1

here is my query:

select `ch`.`id`, `ch`.`caller_number`, `ch`.`destination_number`, `ch`.`caller_origination`, `i`.`id` as `ivr_id`, `i`.`name` as `ivr_name`, `ch`.`ivr_menu_key`, `q`.`id` as `queue_id`, `q`.`name` as `queue_name`, `ch`.`queue_ordered_call`, COALESCE(ch.queue_start, UNIX_TIMESTAMP(ch.created_at)) AS called_at, COALESCE(ch.queue_end - ch.queue_client_join, 0) AS time_in_queue, IF (ch.hangup_cause = "undef_exit_NO_ROUTE_DESTINATION", 1, 0) AS is_diverting_failed, IF (ch.hangup_cause = "customer_hangup" OR ch.hangup_cause = "undef_exit_NORMAL_CLEARING", 1, 0) AS is_forced, "1" AS `cnt` from `calls_history` as `ch` left join `ivrs` as `i` on `ch`.`ivr_id` = `i`.`sip_id` left join (select `id`, id + 200 as extension, `name`, 'queue' as type from `queues`) AS q on `ch`.`destination_number` = `q`.`extension` where `ch`.`client_join` = '0' and `ch`.`hangup_cause` <> 'call_successful' and ch.destination_number BETWEEN 200 AND 1000 and (`ch`.`queue_start` >= '1492549200' or `ch`.`created_at` >= '2017-04-19 00:00:00') and (`ch`.`queue_start` <= '1492635599' and `ch`.`created_at` <= '2017-04-19 23:59:59')

and error:

SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'lower' (SQL: select `ch`.`id`, `ch`.`caller_number`, `ch`.`destination_number`, `ch`.`caller_origination`, `i`.`id` as `ivr_id`, `i`.`name` as `ivr_name`, `ch`.`ivr_menu_key`, `q`.`id` as `queue_id`, `q`.`name` as `queue_name`, `ch`.`queue_ordered_call`, COALESCE(ch.queue_start, UNIX_TIMESTAMP(ch.created_at)) AS called_at, COALESCE(ch.queue_end - ch.queue_client_join, 0) AS time_in_queue, IF (ch.hangup_cause = "undef_exit_NO_ROUTE_DESTINATION", 1, 0) AS is_diverting_failed, IF (ch.hangup_cause = "customer_hangup" OR ch.hangup_cause = "undef_exit_NORMAL_CLEARING", 1, 0) AS is_forced, "1" AS `cnt` from `calls_history` as `ch` left join `ivrs` as `i` on `ch`.`ivr_id` = `i`.`sip_id` left join (select `id`, id + 200 as extension, `name`, 'queue' as type from `queues`) AS q on `ch`.`destination_number` = `q`.`extension` where `ch`.`client_join` = 0 and `ch`.`hangup_cause` <> call_successful and ch.destination_number BETWEEN 200 AND 1000 and (`ch`.`queue_start` >= 1492549200 or `ch`.`created_at` >= 2017-04-19 00:00:00) and (`ch`.`queue_start` <= 1492635599 and `ch`.`created_at` <= 2017-04-19 23:59:59) and (LOWER(`calls_history` as `ch.caller_number`) LIKE %% or (q.name like %% or ch.caller_origination like %% or ch.destination_number like %%) or (FROM_UNIXTIME(COALESCE(ch.queue_start, UNIX_TIMESTAMP(ch.created_at)), '%Y-%m-%d %H:%i:%s') like %%)))

BUT with older version v6.22.0 works perfectly! Please fix the problem :)

[yajra]

As per sql, it seems like the query generated have problem when using alias:

and (LOWER(`calls_history` as `ch.caller_number`) LIKE %% 

Will check further when I got the chance but please do not hesitate to submit a PR if you can to fix the issue. Thanks!

[Dewilas]

any news?

[yajra]

@Dewilas is it possible if you can provide a basic snippet to reproduce the error?

Tried to reproduce using this join demo by adding column alias and all works fine on my side using v6.28.1

        $posts = Post::join('users', 'posts.user_id', '=', 'users.id')
                     ->select([
                         'posts.id as id',
                         'posts.title',
                         'users.name as name',
                         'users.email',
                         'posts.created_at',
                         'posts.updated_at',
                     ]);

        return Datatables::of($posts)
                         ->editColumn('title', '{!! str_limit($title, 60) !!}')
                         ->editColumn('name', function ($model) {
                             return \HTML::mailto($model->email, $model->name);
                         })
                         ->make(true);

[MatiasSW]

Hello, I am facing the same issue, however in my case its caused on a eager load relationship where the LOWER function is being used on a table rather than a field while doing a global search

(select count(1) 
            from `documentos` as `laravel_reserved_0` 
            inner join `doc_referencia` on `laravel_reserved_0`.`id` = `doc_referencia`.`referencia_id` 
            where `doc_referencia`.`documento_id` = `documentos`.`id` and 
            LOWER(`documentos` as `laravel_reserved_0.folio`) LIKE '%bahu%') >= 1 or 
        LOWER(`documentos`.`created_at`) LIKE '%bahu%'

Seems to be an issue with Laravel query builder relationships rather than datatables itself.

[olimortimer]

I'm having a similar problem when creating a column made up of two fields. For example, when I create a column called name but my fields are firstname and surname:

return Datatables::of($user)
                ->editColumn('name', function ($user) {
                    return '<a href="' . route('user.show', $user->id) .'">'.$user->firstname.' '.$tenant->surname.'</a>';
                })
                ->rawColumns(['name'])
                ->make(true);

When searching in the search field, it throws the following:

SQLSTATE[42S22] Column not found: 1054 Unknown column 'users.name' in 'where clause'

EDIT

I've just found the solution to my issue:

->filterColumn('name', function($query, $keyword) {
                    $query->whereRaw("CONCAT(user.firstname, ' ', user.surname) LIKE ?", ["%{$keyword}%"]);
                })

https://datatables.yajrabox.com/eloquent/post-column-search

[phpguru]

In MyDataTables class extends DataTables in the query( ) method:

...
->select('tablename.fieldname as customlabel', ...)
...

In mydatatable.blade.php view, in the columns definition for the aliased field:

...
    {
            "name": "tablename.fieldname AS customlabel",
            "data": "customlabel",
            "title": "Custom Label",
            "orderable": true,
            "searchable": true
    },
...

Works for me, with the search box server side ajax.

    "laravel/framework": ">=5.5",
    "yajra/laravel-datatables-oracle": "^6.0",

[yajra]

@phpguru It should be like this on your js:

...
    {
            "name": "tablename.fieldname",
            "data": "customlabel",
            "title": "Custom Label",
            "orderable": true,
            "searchable": true
    },
...