Laravel Eloquent Sort by Relation Table Column

Laravel Eloquent sort by relation table column

Eager loading uses separate queries so you need join for this:

$products = Shop\Product::join('shop_products_options as po', 'po.product_id', '=', 'products.id')
   ->orderBy('po.pinned', 'desc')
   ->select('products.*')       // just to avoid fetching anything from joined table
   ->with('options')         // if you need options data anyway
   ->paginate(5);

SELECT clause is there in order to not appending joined columns to your Product model.

---

edit: as per @alexw comment - you still can include columns from joined tables if you need them. You can add them to select or call addSelect/selectRaw etc.

How to filter by relation column Laravel

with() are using eager loading, which turns this into two queries.

You need to use join() instead of with()

$orders = Order
        ::join('customers', 'order.customer_id', '=', 'customers.id')
        ->orderBy('customers.first_name')
        ->get();

OR

You may use sortBy() or sortByDesc() methods of Collection.

For example:

$orders = Order
        ::with('customer')
        ->get()
        ->sortBy('customer.first_name');

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,
            ]
        );
    }
}

Laravel Eloquent: How to order results of related models?

You have a few ways of achieving this:

// when eager loading
$school = School::with(['students' => function ($q) {
  $q->orderBy('whateverField', 'asc/desc');
}])->find($schoolId);

// when lazy loading
$school = School::find($schoolId);
$school->load(['students' => function ($q) {
  $q->orderBy('whateverField', 'asc/desc');
}]);

// or on the collection
$school = School::find($schoolId);
// asc
$school->students->sortBy('whateverProperty');
// desc
$school->students->sortByDesc('whateverProperty');

// or querying students directly
$students = Student::whereHas('school', function ($q) use ($schoolId) {
  $q->where('id', $schoolId);
})->orderBy('whateverField')->get();

---

Related Topics