CODEWITHSUNDEEP
phplaraveleloquent
Paul
Paul

Reputation: 858

Laravel Eloquent - sorting parent based on child relationship attribute

I have a Product which has multiple Variants.

I want to select all Products and sort them based on the price of first underlying variant child.

Product A

Product B

Product C

In this case if i want to order on price asc i expect to get the following result ordering:

My current (not working) query:

Product::with(['variants' => function ($q) {
   $q->orderBy('price', 'desc');
}])->orderBy('variants.price', 'desc')->get();

Upvotes: 4

Views: 2989

Answers (3)

Quân Hoàng
Quân Hoàng

Reputation: 411

You can not order by when using with(): Because with() will make two separate queries like this:

SELECT * FROM users;
SELECT * FROM roles WHERE id IN (1, 2, 3, 4, whatever...);

The solution for your problem is use join() so you make only one query:

$order = 'desc';
$products = Product::join('variants', 'variants.product_id', '=', 'products.id')
    ->orderBy('products. price', $order)
    ->select('products.*')
    ->paginate(10);

Upvotes: 2

Paul
Paul

Reputation: 858

Solved it by using the following query:

Product::join('variants', function ($join) {
   $join->on('variants.product_id', '=', 'products.id');
})
   ->select('products.*', DB::raw("MAX(variants.price) as max_variant_price"))
   ->groupBy('variants.product_id')
   ->orderBy('max_variant_price')
   ->get();

Upvotes: 1

Tharaka Dilshan
Tharaka Dilshan

Reputation: 4499

You can Order them after the fetch.

$products = Product::with('variants')
    ->get()
    ->sortBy(function($product) {
        // if you want to order by first found
        return $product->variants->first()->price;

        // if you want to order by max price
        return $product->variants->max->price;

        // or min price
        return $product->variants->min->price;

        // or average price
        return $product->variants->average->price;
    });

Upvotes: 0

Related Questions