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();
On datainfinities.com, Read articles all around JavaScript, React, Node.js, PHP, Laravel, and Shopify.
Related Blogs
How to display validation error messages in Laravel blade view
How to solve error: array_merge() does not accept unknown named parameters
419 page expired error in Laravel
Laravel Cron Job Task Scheduling Tutorial
Laravel Model Events and Observers Tutorial
How to create multiple where clause query using Laravel eloquent
The POST method is not supported for this route. Supported methods: GET, HEAD
Delete an element from an array in PHP
Explore All Blogs