anon40457's avatar

Order by the difference of two columns in Laravel 5.3

I have an eloquent query where one of the orderBy is the difference of two columns.

$mymodel = Level::where([['ColA', 5], ['ColB', 10], ['ColC', 7]])
                 ->orderBy('ColA', 'Desc')
                 ->orderBy('ColA' - 'ColB', 'Desc')
                 ->orderBy('ColC', 'Desc')
                 ->orderBy('ColD', 'Asc')
                 ->pluck('userId')->toArray();

The exact same code on localhost with sqlite works without an error. But on production with MySQL has the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause' (SQL: select `userId` from `levels` where (`ColA` = 5 and `ColB` = 10 and `ColC` = 7) order by `ColA` desc, `0` desc, `ColC` desc, `ColD` asc)
0 likes
3 replies
jlrdw's avatar

Probably need an "as" clause ('ColA' - 'ColB') as whatever

1 like
javaditco's avatar

You can query like this :

$mymodel = Level::where(<Your condition>)->select('*',DB::raw(' `ColA` - `ColB` AS  ABDiff'))->orderBy('ABDiff', 'Desc')->get();
lostdreamer_nl's avatar

Or use the orderByRaw method:

orderByRaw('(updated_at - created_at) desc')
$mymodel = Level::where([['ColA', 5], ['ColB', 10], ['ColC', 7]])
                 ->orderBy('ColA', 'Desc')
                 ->orderByRaw('(ColA - ColB) desc')
                 ->orderBy('ColC', 'Desc')
                 ->orderBy('ColD', 'Asc')
                 ->pluck('userId')->toArray();
1 like

Please or to participate in this conversation.