how to make orderBy ignore value 0 in eloquent laravel

Question

My problem is that I have my collection that has a position field and I want it to be sorted in ascending order, but the fields that have the value null or 0 by default are detected as smaller than those that have an index.

My question is how can I make orderBy ignore the value 0 or null.

$listing = Product::get();
$listing = $listing->orderBy('order','ASC');

`0` _is_ smaller than 1, 2, etc. If you want 0/null to be floated to the end of the list, you'll need to do a custom clause, which depends on your SQL driver (MySQL, Postgres, etc.) Also, `$listing = Product::orderBy('order', 'ASC')->get();`. The way you have it right now doesn't work (or shouldn't work). — Tim Lewis, Sep 08 '22 at 19:39

score 0 · Accepted Answer · answered Sep 08 '22 at 19:54

You could use CASE in your orderBy as a hack to "ignore" 0 (place it last).

$listing = Product::query()
    ->orderByRaw('CASE WHEN "order" = 0 THEN 0 ELSE 1 END DESC, "order" ASC')
    ->get();

You can also split it if you prefer.

$listing = Product::query()
    ->orderByRaw('CASE WHEN "order" = 0 THEN 0 ELSE 1 END DESC')
    ->orderBy('order')
    ->get();

$listing = Product::query()
    ->orderByDesc(DB::raw('CASE WHEN "order" = 0 THEN 0 ELSE 1 END'))
    ->orderBy('order')
    ->get();

score 0 · Answer 2 · answered Sep 08 '22 at 20:34

$listing = Product::orderByRaw('-order DESC')->get();

There is a minus sign before the column. Instead of asc, we are now sorting as desc, this is because we have inverted the values in #2 and so the sorting must also be inverted now to get the right results.

this working fine for me.

how to make orderBy ignore value 0 in eloquent laravel

2 Answers2