Using two different orderBy logic based on a database field (null/not null)

Question

I am trying to orderBy() my query based on if a field is NULL or not.

So, if minute field is null, I want to orderBy id; otherwise I want to use couple of different set of orderBy.

This shows the "minute not NULL" scenario:

public function events() {
   return $this->hasMany(Event::class)
        ->orderBy('minute', 'desc')
        ->orderByRaw('type = ? desc', 'period')
        ->orderByDesc('external_event_id')
}

What I want to achieve is something like this pseudo code:

public function events() {
   return $this->hasMany(Event::class)
        ->when('minute is NULL', function($q) {
            $q->orderByDesc('id');
        })
        ->when('minute is NOT NULL', function($q) {
            $q->orderBy('minute', 'desc')
              ->orderByRaw('type = ? desc', 'period')
              ->orderByDesc('external_event_id');
        });
}

Is there a way to achieve this using Laravel query builder?

Doesn't seem logical, the minute field is a column of events table right? And it's possible that different rows/records may have different values or some can have null values as well. Ordering is done on entire collection of result rows. Imagine that even if ordering could be changed based on column value - ordering would change multiple times in a set of say 100 rows. So that's not logical to implement — Donkarnash, Dec 09 '20 at 17:32
if my answer worked out perfectly let me know becaue i have not tested — Abilogos, Dec 09 '20 at 17:48
@Donkarnash hmm, I see your point, but it's a little different for me. Let's say my model is called "Bus". For some Bus events, I know all the minute fields are null. For others the minute information exists. So my idea makes sense in this case, no? — senty, Dec 09 '20 at 17:50
In that case I think the answer by @Abilogos below is the closest that you can get with the desired results. It is basically bifurcating the query into two getting all records with minute as null and ordering it by id then another query to get all records where minute is not null and ordering it by a different set of orderings and then merging the results. — Donkarnash, Dec 09 '20 at 17:52
Yeah, @Abilogos answer makes sense but unfortunately `unionAll()` messes up the ordering. I also tried `union()` but ordering gets lost again. — senty, Dec 09 '20 at 17:58
You can execute the individual queries and then concat the resulting collections docs:https://laravel.com/docs/8.x/collections#method-concat — Donkarnash, Dec 09 '20 at 17:59
@senty Have posted an answer with a hackish workaround. Let me know if it works for you — Donkarnash, Dec 09 '20 at 18:52

Donkarnash · Answer 1 · 2020-12-09T19:20:21.213

A dirty hack which can get close to what you desire

Define two different relations for events on the model

public function eventsWithNullMinute()
{
    return $this->hasMany(Event::class)
        ->whereNull('minute')
        ->orderByDesc('id');
}

public function eventsWithNonNullMinute()
{
    return $this->hasMany(Event::class)
        ->whereNotNull('minute')
        ->orderBy('minute', 'desc')
        ->orderByRaw('type = ? desc', 'period')
        ->orderByDesc('external_event_id');
}

And define a method on the model to get all events merged under events key

public function events()
{
    return static::with(['eventsWithNullMinute', 'eventsWithNonNullMinute'])
        ->where('id', $this->id)
        ->get()
        ->map(function($record){
            $events = collect([]);
            if($record->eventsWithNullMinute->count()) {
                $events->push($record->eventsWithNullMinute);
                unset($record->eventsWithNullMinute);
            }

            if($record->eventsWithNonNullMinute->count()) {
                $events->push($record->eventsWithNonNullMinute);
                unset($record->eventsWithNonNullMinute);
            }

            $record->events = $events;

            return $record;
        })->pluck('events');
}

Thanks, again would work but it's pretty dirty indeed. I'll sleep on it and re-think. — senty, Dec 09 '20 at 19:52

Abilogos · Answer 2 · 2020-12-09T18:02:23.180

-1

query builder has whereNull and whereNotNull function you can build each query and union them like:

public function events() {
   $q1 =  $this->hasMany(Event::class)
        ->whereNull('minute')->orderByDesc('id')->get();
        
   $q2 =   $this->hasMany(Event::class)
        ->whereNotNull('minute')
         ->orderBy('minute', 'desc')
         ->orderByRaw('type = ? desc', 'period')
          ->orderByDesc('external_event_id')->get();

    //collection union
    return $q1->union($q2);
}

edited Dec 09 '20 at 18:02

answered Dec 09 '20 at 17:43

Abilogos

4,777
2
19
39

I tried your code. Although it makes sense, `->unionAll();` messes up the ordering. If I try `return $q1` or `return $q2` individually, it works though. Any other suggestion to replace `unionAll()` ? – senty Dec 09 '20 at 17:56
yes, you right ordering is none sense if there are two different rules whenever you specify which one is prioritize than other rules set. for example first set has higher priority. – Abilogos Dec 09 '20 at 17:58
i suggest to first `->get()` the first , then `->get()` the other, then union collections, then return them all – Abilogos Dec 09 '20 at 17:59
Again, would work but it's more ideal to do it in query level rather than doing 2 separate DB transactions and sorting in collection level. Using `get()` twice will mess up the relationship too :/ – senty Dec 09 '20 at 18:02
yes. but if you want to use one query it can be achievable with **STORE PROCEDURE** i guess. i dont think, with query builder we can go further – Abilogos Dec 09 '20 at 18:03
1

Check this approach: https://stackoverflow.com/a/17644303/4705339 – senty Dec 09 '20 at 18:09
but you cannot make a constant like a million to your query since you want different ordering rule – Abilogos Dec 09 '20 at 18:13

Using two different orderBy logic based on a database field (null/not null)

2 Answers2