Laravel Eloquent - sorting parent based on child relationship attribute

Question

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

• Variant A1 (price: $3)
• Variant A2 (price: $3)

Product B

• Variant B1 (price: $2)
• Variant B2 (price: $2)

Product C

• Variant C1 (price: $4)
• Variant C2 (price: $4)

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

• Product B
• Product A
• Product C

My current (not working) query:

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

Answer 1

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);

Answer 2

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();