Laravel Order by Pivot Table Field

In this article we discuss:

• How to sort results by pivot table field in eager loading
• How to sort results using orderByPivot in lazy loading

The pivot table is an intermediate table for many-to-many relationships.  Let us see how we can sort many-to-many relationship results with a field in the pivot table. For this, we can take an example of a recipe and ingredients relationship with a quantity field in the pivot table.

Table Structure

Defining relationships in models

File: App\Models\Recipe.php

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Recipe extends Model
{
    use HasFactory;
    public function ingredients()
    {
        return $this->belongsToMany(Ingredient::class, "recipe_ingredient");
    }
}

File: App\Models\Ingredient.php

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Ingredient extends Model
{
    use HasFactory;
    public function recipes()
    {
        return $this->belongsToMany(Ingredient::class, 'recipe_ingredient');
    }
}

How to sort results by pivot table in eager loading

In the eager loading, we fetch all data with relationship records in a single query as below.

$recipe = Recipe::with(['ingredients' => function ($q) {
        $q->orderBy('quantity', 'ASC');
}])->find($id); 

Here ingredients is a relationship we defined in the Recipe model and quantity is a field in the pivot table recipe_ingredient

How to sort results using orderByPivot in lazy loading

In lazy loading run a SQL query to get the relationship record using orderByPivot() as given below.

$recipe = Recipe::findOrFail($id);
$ingredients = $recipe->ingredients()->orderByPivot('quantity', 'ASC')->get();        

Or we can write in a single line like below:

$ingredients = Recipe::findOrFail($id)->ingredients()->orderByPivot('quantity', 'ASC')->get();