CODEWITHSUNDEEP
phpsqllaraveleloquent
Łukasz Szcześniak
Łukasz Szcześniak

Reputation: 1444

Laravel scope with union

I need a way to sort my models by two columns (but not as order by x, y does). I have a priority column that can 'boost' a model in results, and standard created_at that should be sorting key when priority is not set. The only way I have found to do this was with unions.

With query like this:

(SELECT * FROM `charts` WHERE priority IS NOT NULL ORDER BY priority ASC) 
UNION (SELECT * FROM `charts` WHERE priority IS NULL AND created_at IS NOT NULL ORDER BY CREATED_AT ASC) 
UNION (SELECT * FROM `charts` WHERE created_at IS NULL)

So I tried to do something like:

public function scopeSortPriority($query)
{
    return $query->whereNotNull('priority')
        ->orderBy('priority')
        ->union($query->whereNull('priority')
            ->whereNotNull('created_at')
            ->orderBy('created_at'))
        ->union($query->whereNull('priority')
            ->whereNull('created_at'));
}

But it sadly does not work.

Is there an other way to accomplish what I want in SQL? Otherwise - how should I rebuild this scope to make it work?

Upvotes: 3

Views: 1250

Answers (1)

Nerea
Nerea

Reputation: 2147

The problem is that you are using $query in your unions.

If you know that the common part of your query and your unions allways will be \DB::table('charts') you can do:

$query->whereNotNull('priority')
    ->orderBy('priority')
    ->union(\DB::table('charts')->whereNull('priority')
        ->whereNotNull('created_at')
        ->orderBy('created_at'))
    ->union(\DB::table('charts')->whereNull('priority')
        ->whereNull('created_at'));

If $query will be the same but can change you can do:

    $query2 = clone $query;
    $query3 = clone $query;

    $query->whereNotNull('priority')
    ->orderBy('priority')
    ->union($query2->whereNull('priority')
        ->whereNotNull('created_at')
        ->orderBy('created_at'))
    ->union($query3->whereNull('priority')
        ->whereNull('created_at'));

In both case the output of the sql is:

(select * from `charts` where `priority` is not null order by `priority` asc) union (select * from `charts` where `priority` is null and `created_at` is not null order by `created_at` asc) union (select * from `charts` where `priority` is null and `created_at` is null)

Upvotes: 5

Related Questions