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 NameStructure
recipes
idbigint(20)
recipe_name varchar(191)
ingredients
idbigint(20)
ingredient_namevarchar(191)
recipe_ingredient
recipe_idbigint(20)
ingredient_idvarchar(191)
quantityint(11) 

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

Related Blogs

Limit Text in Laravel

Limit Text in Laravel

Limit text in Laravel can be done using the Str class from Illuminate\Support\Str namespace for truncating text by character count and word count.

419 page expired Laravel error

419 page expired error in Laravel

419 page expired error in Laravel occurs when the valid CSRF token is missing in the post request. Laravel checks the CSRF token in VerifyCsrfToken middleware.

Delete an element from an array in PHP

Delete an element from an array in PHP

To delete an element from an array in PHP, use built-in functions unset(), array_splice(), or array_diff(). Learn how to delete elements using these functions.

Create Multiple Where Clause Query Using Laravel Eloquent

How to create multiple where clause query using Laravel eloquent

Multiple where clauses can create by chaining the where() method. You can use orWhere, whereNot, etc to create complex where queries in Laravel Eloquent.

What's New in PHP 8

What's New in PHP 8(Features, Changes, Security & JIT compiler)

PHP 8 new features and functions include named arguments, union types, attributes, constructor property promotion, match expression, nullsafe operator, Saner string to number comparisons, Saner Numeric Strings, and JIT Compiler.

Regular Expression

Most Common Regular Expressions - email, URL, strong password, credit cards, number systems and dates

Regular expressions for email, URL, strong password, credit cards, number systems, dates and more.

Conditional Tags in Shopify Liquid

if, unless, elsif/else and case/when Conditional Tags in Shopify Liquid

Control Flow or Conditional Tags determines what content should be rendered based on given conditions. if, unless, elsif/else, case/when are conditional tags.

useHref() may be used only in the context of a Router component

useHref() may be used only in the context of a Router component

The error "useHref() may be used only in the context of a <Router> component” occur when we use <Link> component outside the Router context in React Router.

How to Fix error:0308010C:digital envelope routines::unsupported

How to Fix error:0308010C:digital envelope routines::unsupported

To fix the error:0308010C:digital envelope routines::unsupported, enable the legacy provider for Node.js by passing --openssl-legacy-provider flag to webpack.