Zoefschildpad
3 days ago

Ordering by the results of a whereIn

Posted 3 days ago by Zoefschildpad

As a simplified example, I have a database full of fruit and a fruit model. They have the fields 'name' and 'expiration_date'. I have an array $tropicalFruits which contains the names of all fruits considered tropical. What I want is a query (preferably through a scope) that returns the tropical fruits first, sorted by expiration_date, and then the other fruit, also sorted by expiration date. (in reality there are three groups)

I tried the following:

public function scopeFruitSorting($query) {
    $query->orderBy('expiration_date');
    $query2 = (clone $query)->whereNotIn('name', $tropicalFruits);
    return $query->whereIn('name', $tropicalFruits)->union($query2);
}

This gets me the results I want. However, if I apply any more wheres or scopes after that, they are only applied to one of the two queries.

Is there a nice way to do what I'm trying to do? I need to paginate it, so I can't just do two queries and merge the collections.

Please sign in or create an account to participate in this conversation.