Laravel Eloquent model order by local column and relation models column value

Question

I have 2 models:

Product, which has sort field.
Stock, which has product_id and quantity fields.

I want to get products ordered by sort field (ASC) but the products which stocks quantity equals 0 need to be in the end of list.

E.g.

Products

id     sort
1      3
2      4
3      2
4      1

Stock

product_id    quantity
1             2
1             3
2             5
3             0
4             2

Expected List (Product IDs) - 4, 1, 2, 3

I have written the SQL query for what i need to do.

select p.url, p.sort, s.tot
from products p
inner join (select product_id, sum(quantity) as tot from stocks group by product_id) s on p.id = s.product_id
order by case when s.tot = 0 then 1 else 0 end, p.sort asc

Here is the relation of Product -> Stock

public function stocks() {
    return $this->hasMany('\App\Stock', 'product_id');
}

Is there a way to get this kind of listing using Eloquent Model's scopes?

UPDATE

The solution.

public function scopeOrdered($query, $notInStock = false)
    {
        if($notInStock)
            return $query->join(DB::raw("(
                select product_id, sum(quantity) as tot
                from stocks
                group by product_id
                ) s"), 'products.id', '=', 's.product_id')->orderByRaw("case when s.tot = 0 then 1 else 0 end, products.sort ASC");
        else
            return $query->orderBy('sort', 'asc');
    }

Ali Sharifineyestani Ali Sharifineyestani · Accepted Answer · 2019-09-05T18:12:44

You can use merge method. But I don't think your database design is correct. I think It is better you put quantity field on the product table. If you want to keep this design for any reason it's better, you set Unique index for product_id in stocks table. For example, the one of your products(id=1) now has two quantity. That's not possible in the real world

    $query1 = Product::whereHas('stocks' , function ($q) {
        $q->where('quantity', '<>',  0);
    })->with('stocks')->orderBy('sort','asc')->get();

    $query2 = Product::whereHas('stocks' , function ($q) {
        $q->where('quantity', 0);
    })->with('stocks')->get();


    $merged = $query1->merge($query2);

    return $merged;

And you can have your query as in the example below:

    $products = DB::table('products')
        ->join('stocks', 'stocks.product_id', '=', 'products.id')
        ->selectRaw(' product_id, sum(quantity) as tot')
        ->groupBy('product_id')
        ->orderByRaw(
            "CASE WHEN quantity = 0 THEN 1 ELSE 0 END ASC"
        )
        ->get();



    return $products;

Laravel Eloquent model order by local column and relation models column value

1 Answers