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
Table Name | Structure | ||||||
recipes |
| ||||||
ingredients |
| ||||||
recipe_ingredient |
|
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();