Column not found for version 12.1.1 and 12.1.2

[alexbog8]

Hi,

When I use version 12.1.1 or version 12.1.2 I get column not found , unknown column in order clause. Version 12.1.0 and below works just fine.
What can be the problem? thanks.

[alexbog8]

So I have something like this on the querybuilder

DB::raw(' ( CASE
WHEN end >= CURDATE() THEN (DATEDIFF(end, CURDATE()) )
WHEN end < CURDATE() THEN (DATEDIFF(end, CURDATE()) - DATEDIFF(end, CURDATE()) - DATEDIFF(end, CURDATE()) + '.$diff_in_days.' )
END ) as diff_order')

then javascript
{ data: 'date', name: 'diff_order', 'searchable': false} and

order: [[6, "asc"]],

In version 12.1.0 this works, but from version 12.1.1 no, with the error column not found workshops.diff_order

The quick fix is to order in the querybuilder and remove in javascript order: [[6, "asc"]],

[yajra]

Always alias change was introduced in #3227 but I think we overlooked the use case when using raw SQL.

Tagging for review and @Seb33300.

Thanks!

[yajra]

For a workaround, maybe try using orderColumn

[Seb33300]

It looks like the alias is not properly detected.
I will try to reproduce tomorrow.

[Seb33300]

@alexbog8 can you please confirm the exact code passed in select?

DB::raw(' ( CASE
WHEN end >= CURDATE() THEN (DATEDIFF(end, CURDATE()) )
WHEN end < CURDATE() THEN (DATEDIFF(end, CURDATE()) - DATEDIFF(end, CURDATE()) - DATEDIFF(end, CURDATE()) + '.$diff_in_days.' )
END ) as diff_order')

Something I am thinking that may cause that bug can be a double space between as diff_order => as diff_order

Can you please share how you added it to the query builder?

[alexbog8]

This is the query builder

$data = Workshop::select(['*', 'workshops.id as id', DB::raw(' ( CASE WHEN end >= CURDATE() THEN (DATEDIFF(start, CURDATE()) ) WHEN end < CURDATE() THEN (DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) + '.$diff_in_days.' ) END ) as diff_order, ( CASE WHEN end >= CURDATE() THEN (DATEDIFF(end, CURDATE()) ) WHEN end < CURDATE() THEN (DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) + '.$diff_in_days.' ) END ) as diff_order2'), ]) ->leftJoin('event_types', 'event_types.id', '=', 'workshops.event_type');

I checked, there is no double space between as and diff_order. Thanks for your help

[Seb33300]

Hi @alexbog8,

Thanks, this helped me to identify the bug.
This is because you added 2 columns (diff_order and diff_order2) inside the same DB::raw().
I will submit a PR to fix that case.

Until the fix is published, a workaround is to split your DB::raw() into 2 DB::raw():

Workshop::select([
    '*', 
    'workshops.id as id', 
    DB::raw(' ( CASE WHEN end >= CURDATE() THEN (DATEDIFF(start, CURDATE()) ) WHEN end < CURDATE() THEN (DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) + '.$diff_in_days.' ) END ) as diff_order'), 
    DB::raw(' ( CASE  WHEN end >= CURDATE() THEN (DATEDIFF(end, CURDATE()) ) WHEN end < CURDATE() THEN (DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) - DATEDIFF(start, CURDATE()) + '.$diff_in_days.' )  END ) as diff_order2'), 
])

[alexbog8]

Thanks, I confirm that splitting the DB::raw() works now.