Laravel order by date, null-dates always at the end

Question

I have to sort orders on my date field send_date, but NULL send_dates always on the end.

I have tried this, but this isn't working:

Order::orderBy(\DB::raw('ISNULL(send_date), send_date'), 'asc')
->orderBy('send_date', 'desc')

How can I sort on send_date, with NULL dates always on the end?

https://stackoverflow.com/questions/17644072/how-to-sort-null-values-last-using-eloquent-in-laravel?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Devsi Odedra, May 14 '18 at 07:34
Make sure in DB you have `null` not empty value `''` or spaces — M Khalid Junaid, May 14 '18 at 07:42
@DevsiOdedra I've read that solution, but it isn't working because I have no order column, but only a date column — angelique000, May 14 '18 at 07:44

score 1 · Answer 1 · answered May 14 '18 at 07:47

1

Just use the -minus sign beforet the Column name.

Order::orderByRaw("-start_date",'DESC');

It will order Sort the null values at the end.

Hope this helps.

answered May 14 '18 at 07:47

FULL STACK DEV

score 1 · Answer 2 · answered May 14 '18 at 07:56

1

You can do :

Order::orderBy(\DB::raw("COALESCE(d, '9999-12-31')", 'ASC');

This works because if the date is NULL it's considered to be 9999-12-31 which according to the docs is the largest possible date

Note: This only works if you sort ASC but if you sort DESC NULLs are at the end anyway.

answered May 14 '18 at 07:56

apokryfos

Sorry, it doesn't work, because when I filter DESC, the nullable dates have to be also on the end – angelique000 May 14 '18 at 08:00
If you sort by DESC the nulls go to the end anyway. – apokryfos May 14 '18 at 08:01
http://sqlfiddle.com/#!9/952214/9 the nullable doesn't go to the end... – angelique000 May 14 '18 at 08:03
There's no magic value that will send NULLs always at the end regardless of sort direction. You can use this code when sorting `ASC` and `orderBy(d, 'DESC')` when sorting by `DESC`. – apokryfos May 14 '18 at 08:06
http://sqlfiddle.com/#!9/952214/13 will work in both cases but as you can see it's ridiculously complicated and cannot be done using eloquent at all (it can be done using the query builder though) – apokryfos May 14 '18 at 08:11
Is it possible to create a new "order_column"; in mysql: if (send_date = null order_column = 1) ? – angelique000 May 14 '18 at 08:13
Here's the problem. If you `ORDER BY ... DESC` what you will get is **the reverse** of what you get if you `ORDER BY ... ASC` there's no magic value that always ends up in the bottom when doing both ASC and DESC ordering as far as I know. – apokryfos May 14 '18 at 08:17

score 0 · Answer 3 · answered May 14 '18 at 07:49

0

Order::orderBy("start_date",'DESC');

i think it will help

answered May 14 '18 at 07:49

Kuldeep Mishra

No it doesn't work, because: sometimes I have to order my column on send_date asc, and sometimes send_date desc, but ALWAYS the nullable send_date have to be on the end... – angelique000 May 14 '18 at 07:53

3 Answers3