Sorting laravel collection by leaving null/empty last

Question

Can't seem to get my head around of sorting laravel collection so empty / null data would end up being last. ( bit confused about usort )

Pretty much all I have is bunch of times / timestamps that need to be ordered. Some rows may not have for that column.

I would like data to appear ASC / ascending while empty/null data is shown last.

$collection->sortBy('timestamp') sorts nicely but doesn't know how to deal with empty fields.

Table looks like this.

   $data = $data->sort(function($a, $b) use ($sortBy) {
        if ($a->{$sortBy} and $b->{$sortBy}) return 0; 
        return ($a->{$sortBy} > $b->{$sortBy}) ? -1 : 1;
    });

Random code I tried from the internet, which I can't get to work correctly. $sortBy contains a field name to sort by ( since it may change ) Faulty code deals with empty / null data but its out of order.

Please add code you are using for sorting. – Pankit Gami Apr 17 '17 at 10:43 — Pankit Gami, Apr 17 '17 at 10:43

score 7 · Answer 1 · answered Feb 21 '18 at 19:42

Have to use sort() with a closure. Below will sort timestamp ASC with NULL at the end.

$sorted = $collection->sort(function ($a, $b) {
    if (!$a->timestamp) {
        return !$b->timestamp ? 0 : 1;
    }
    if (!$b->timestamp) {
        return -1;
    }
    if ($a->timestamp == $b->timestamp) {
        return 0;
    }

    return $a->timestamp < $b->timestamp ? -1 : 1;
});

score 3 · Accepted Answer · answered Apr 17 '17 at 15:28

3

Try:

$collection->sortBy('-timestamp')

Does it work?

answered Apr 17 '17 at 15:28

Jakub Kratina

644
6
14

This post gave me idea thanks to what I think I got it working. Instead of having sortBy('-timestamp'), I created another variable for each entry for example: timestamp_order to which I assigned by default value, random number of -10000 and that variable gets overwritten if there is any data ( timestamp is not null or empty ). Then sorted by $collection->sortBy('timestamp_order') – m4gix1 Apr 17 '17 at 18:48
I don't like that idea, but glad it works :) If my suggestion doesn't work, maybe try using CASE, like following example: `ORDER BY (CASE WHEN timestamp IS NULL then 1 ELSE 0 END), timestamp` Take a look here: http://stackoverflow.com/questions/5993109/order-by-asc-with-nulls-at-the-bottom – Jakub Kratina Apr 17 '17 at 20:10
Didn't like that idea too much either, but since I have many relations atm and limited time frame to figure it out then I'm sorting collection instead. Changed the random number to Carbon::now()->addYear()->toDateTimeString(); instead and since they are older than any other timestamps then they appear last. – m4gix1 Apr 19 '17 at 15:01

score 3 · Answer 3 · answered Oct 26 '20 at 13:44

I had a similar issue. In my case, the time attribute of a $result might be NULL. It acts as if NULL is 0 (as int) and that's expected behavior. But I also wanted to sort the collection by leaving NULL last.

$collection->sortBy(function ($result) {
    if ($result['time'] === NULL) {
        return PHP_INT_MAX;
    }

    return $result['time'];
});

You can achieve this simply by returning an value higher in the alphabetical order compared to all other values in the array. i.e. PHP_INT_MAX to be safe. This will make sure all the results where the time equals NULL are at the end of the array.

score 1 · Answer 4 · answered May 05 '21 at 14:06

1

Similar to Mr. Speelman's solution, but as a shorter PHP 7.4+ version:

$collection->sortBy(fn($e) => $e->timestamp ?: PHP_INT_MAX)

answered May 05 '21 at 14:06

wivku

2,457
2
33
42

score -3 · Answer 5 · answered Apr 17 '17 at 11:04

-3

I assume your timestamp is unix timestamp.

You can sort it like this :

$sorted = $collection->sortByDesc('timestamp');

answered Apr 17 '17 at 11:04

Pankit Gami

2,523
11
19

2

This wouldn't be what I want to achieve, I'd like results to appear ASC / Ascending while empty / null data is last. – m4gix1 Apr 17 '17 at 11:17

Sorting laravel collection by leaving null/empty last

5 Answers5

Linked