Laravel Eloquent OrderBy show empty/null column value first

Question

Currently I can get all the records with simple orderBy only

But what I'm trying to do is to show all records

if date column either empty or null it should be shown as first and all the following data that doesn't empty/null will follows.

$data = \App\Models\Event::where('deleted_at',NULL)
->orderBy('date','DESC')
->paginate(6);
return $data;

Current Output

id | name | date

3 | text3 | 2020-08-03

2 | text4 | 2020-08-02

4 | text5 | 2020-08-01

1 | text1 |

Example of output trying to achieve

id | name | date

1 | text1 |

2 | text2 | 2020-08-03

3 | text3 | 2020-08-02

4 | text4 | 2020-08-01

As you can see, the date values who blanks are shown first and all the data follows with orderBy date as DESC

https://stackoverflow.com/questions/17644072/how-to-sort-null-values-last-using-eloquent-in-laravel see this link — Ankita Dobariya, Aug 21 '20 at 07:19
Does this answer your question? [mysql order by, null first, and DESC after](https://stackoverflow.com/questions/9307613/mysql-order-by-null-first-and-desc-after) — Matt Komarnicki, Aug 21 '20 at 07:32

score 0 · Answer 1 · answered Aug 21 '20 at 07:17

0

You should use the minus sign with a raw order query:

$data = \App\Models\Event::where('deleted_at',NULL)
  ->orderByRaw('-date','DESC')
  ->paginate(6);
return $data;

answered Aug 21 '20 at 07:17

gbalduzzi

9,356
28
58

Ankita Dobariya · Answer 2 · 2020-08-21T07:30:32.440

0

  $data = \App\Models\Event::where('deleted_at',NULL)->orderByRaw('date','DESC')->paginate(6);

use the orderByRaw() method.

edited Aug 21 '20 at 07:30

answered Aug 21 '20 at 07:25

Ankita Dobariya

823
5
14

score 0 · Answer 3 · answered Aug 21 '20 at 07:53

0

You can use a case for your ordering criteria like

$data = \App\Models\Event::where('deleted_at',NULL)
    ->orderByRaw("case when date is null or date = '' then 1 else 0 end",'DESC')
    ->paginate(6);

answered Aug 21 '20 at 07:53

M Khalid Junaid

63,861
10
90
118

Laravel Eloquent OrderBy show empty/null column value first

3 Answers3