Perform order by relationship field in Eloquent

This will sort the subquery. Not the "first query (the product query)".

Basically, your subquery will be: select * from variants where product_id in (....) order by price, and that is not what you want, right?

<?php 
// ...

$order = $request->sort;

$products = Product::whereHas('variants')->with(['reviews',  'variants' => function($query) use ($order) {
  if ($order == 'price') {
    $query->orderBy('price');
  }
}])->paginate(20);

If you want to sort product +/or variant you need to use join.

$query = Product::select([
          'products.*',
          'variants.price',
          'variants.product_id'
        ])->join('variants', 'products.id', '=', 'variants.product_id');

if ($order == 'new') {
    $query->orderBy('products.created_at', 'DESC');
} else if ($order == 'price') {
    $query->orderBy('variants.price');
}

return $query->paginate(20);

If you want to sort product and variants, you don't need joins, because you'll won't have the related model loaded (like $product->variants), just all the fields of the variants table.

To sort models by related submodels, we can use Eloquent - Subquery Ordering.

To order the whole model by a related model, and NOT the related model itself, we can do it like this:

return Product::with('variants')->orderBy(
    Variants::select('price')
        // This can vary depending on the relationship
        ->whereColumn('variant_id', 'variants.id')
        ->orderBy('price')
        ->limit(1)
)->get();

Perform order by relationship field in Eloquent

Related

Recent Posts