Laravel Eloquent Sort by Relationship

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

How can i sort an laravel eloquent query by one-to-many relation

I solved this problem:

First off all add a hasOne relation to Product model:

public function latestInventory()
{
    return $this->hasOne(Inventory::class);
}

Then in ProductController:

$products = Product::with(['latestInventory' => function($query) {
    $query->where('count', '>', 0)->orderBy('id');
}]);
$products = $products->get();
$products = $products->sortBy('latestInventory.price')->paginate(16);

return $products->values()->toArray();

For Paginate the collection :

In ASP (AppServiceProvider.php) on boot method:

Collection::macro('paginate', function($perPage, $total = null, $page = null, $pageName = 'page') {
        $page = $page ?: LengthAwarePaginator::resolveCurrentPage($pageName);
        return new LengthAwarePaginator(
            $this->forPage($page, $perPage),
            $total ?: $this->count(),
            $perPage,
            $page,
            [
                'path' => LengthAwarePaginator::resolveCurrentPath(),
                'pageName' => $pageName,
            ]
        );
    });

In app/Support/Collection.php:

<?php

namespace App\Support;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection as BaseCollection;
class Collection extends BaseCollection
{
    public function paginate($perPage, $total = null, $page = null, $pageName = 'page')
    {
        $page = $page ?: LengthAwarePaginator::resolveCurrentPage($pageName);

        return new LengthAwarePaginator(
            $this->forPage($page, $perPage),
            $total ?: $this->count(),
            $perPage,
            $page,
            [
                'path' => LengthAwarePaginator::resolveCurrentPath(),
                'pageName' => $pageName,
            ]
        );
    }
}

Related Topics