Laravel is a popular PHP framework widely used by developers for building scalable and robust web applications. One of the essential features that Laravel provides is the ability to work with relational databases. Many Laravel applications have a many-to-many relationship between two entities, and to manage this type of relationship, Laravel provides a pivot table. The pivot table acts as a bridge table that holds the foreign keys of both tables and stores the additional data related to the relationship.
In some cases, developers need to sort the data based on the values stored in the pivot table. For example, suppose you have a Laravel application that manages a music library. In that case, you may have many-to-many relationships between artists and genres, with the pivot table storing additional data such as the year the artist was associated with a particular genre. In this scenario, you may need to sort the artists based on the year they were associated with a particular genre.
A pivot table , also known as an intermediate table , links two tables of many-to-many relationship. It gets its name from the names of the tables it joins. The name combines linked tables (in singular) using an underscore. For example, tags + posts = post_tag and roles + users = role_user.
This tutorial shows you how to sort by pivot table in Laravel. First, it takes you through creating many-to-many relationships and a pivot. Next we will start by discussing how to define a many-to-many relationship with additional data, followed by an explanation of how to retrieve and sort data based on the values stored in the pivot table.
$records = <Model>::find(1)-><relationship>()->orderBy('<record>', '<criterion>')->get();By the end of this article, you will have a better understanding of how to work with pivot tables in Laravel and how to sort data based on the values stored in them.
Different methods for sorting data in Laravel
Laravel provides various methods to sort data based on specific columns in ascending or descending order. Some of the commonly used sorting methods in Laravel are:
orderBy(): This method is used to sort the data based on a single column. It takes the name of the column as its argument and sorts the data in ascending order by default. To sort in descending order, you can pass a second argument 'desc' to the method.latest(): This method is used to sort the data based on the created_at column in descending order. It is equivalent to orderBy('created_at', 'desc').oldest(): This method is used to sort the data based on the created_at column in ascending order. It is equivalent to orderBy('created_at', 'asc').orderByDesc(): This method is used to sort the data based on a single column in descending order. It takes the name of the column as its argument.orderByRaw(): This method is used to sort the data based on a raw SQL expression. You can pass the SQL expression as a string to this method.
Understanding Many-to-Many Relationships
In a many-to-many relationship, many columns can reference many columns. For example, a post can have many categories and a category can have many posts. Likewise, a user can have many roles.
<?php
...
class User extends Authenticatable
{
...
public function roles()
{
return $this->belongsToMany(Role::class);
}
}
![Laravel sort by Pivot Table [5 Methods]](https://www.golinuxcloud.com/wp-content/uploads/create-roles.jpg "Laravel sort by Pivot Table [5 Methods]")
And a role can belong to many users.
<?php
...
class Role extends Model
{
...
public function users()
{
return $this->belongsToMany(User::class);
}
}![Laravel sort by Pivot Table [5 Methods]](https://www.golinuxcloud.com/wp-content/uploads/create-users.jpg "Laravel sort by Pivot Table [5 Methods]")
From the explanation and examples of the many-to-many (user-role) relationship, it is not right to place the user_id or role_id on the roles or users table because that would mean each user or role belongs to one foreign id, respectively.
A pivot table comes in to create the link efficiently.
Understanding Pivot Table
In database management, a pivot table is an intermediate table that connects two or more tables with a many-to-many relationship. It is also known as a junction table or cross-reference table.
A many-to-many relationship occurs when each record in one table is related to multiple records in another table, and vice versa. For example, in a database for an e-commerce website, a product may be associated with multiple categories, and each category may have multiple products.
To represent this type of relationship, you need to create two separate tables: one for products and one for categories. However, you cannot add a category column in the products table or vice versa because it would result in data duplication and inconsistencies.
This is where the pivot table comes in. A pivot table is used to create a many-to-many relationship between the two tables. It has foreign keys to both tables, which are used to connect the records in each table. The pivot table holds the relationships between the two tables, and it allows you to query, add, and remove data efficiently.
For instance, if we take the above example, we can create a pivot table named "product_category" that has columns for the product ID and the category ID. Each row in the pivot table represents a product-category relationship. So, a product can have multiple rows in the pivot table, each with a different category ID, and vice versa.
Using this pivot table, we can easily query products and categories based on their relationships. For instance, we can select all products that belong to a specific category or all categories that a particular product belongs to.
Let's create pivot table for the users and roles relationships.
<?php
...
public function up(): void
{
Schema::create('role_user', function (Blueprint $table) {
$table->foreignId('user_id')->constrained();
$table->foreignId('role_id')->constrained();
});
}
...
We have created a pivot table called role_user with two columns: user_id and role_id. The user_id is a foreign key on the users table, while the role_id references the id on the roles table.
![Laravel sort by Pivot Table [5 Methods]](https://www.golinuxcloud.com/wp-content/uploads/create-pivot-table.jpg "Laravel sort by Pivot Table [5 Methods]")
The underscore separates the model names in alphabetical order. You can override the default behavior by specifying the table name when defining the relationships.
return $this->belongsToMany(Role::class, 'role_user');Eloquent creates the pivot table from the second argument to the belongsToMany() method. Additionally, you can customize the current and related foreign key names using third and fourth arguments, respectively.
return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');The third argument user_id is the foreign key name of the current model you are defining the relationship. On the other hand, role_id is the foreign key name of the model the current model is joining to.
Different methods for sorting by Pivot Table in Laravel
Laravel provides various methods to sort data by using the pivot table. These methods allow you to sort data based on the relationship between two tables. Some of the commonly used pivot table sorting methods in Laravel are:
orderByPivot(): This method is used to sort the data based on a column in the pivot table. It takes the name of the column in the pivot table as its argument.latestPivot(): This method is used to sort the data based on the latest record in the pivot table. It is equivalent to orderByPivot('created_at', 'desc').oldestPivot(): This method is used to sort the data based on the oldest record in the pivot table. It is equivalent to orderByPivot('created_at', 'asc').wherePivot(): This method is used to filter the data based on a column in the pivot table. It takes the name of the column and its value as its arguments.wherePivotIn(): This method is used to filter the data based on multiple values in a column in the pivot table. It takes the name of the column and an array of values as its arguments.
Method 1: Use orderBy() Method
<?php
use App\Models\User;
use Illuminate\Support\Facades\Route;
Route::get('/', function () {
echo " ******** Before Laravel sort by pivot table ******** <br>";
$roles = User::find(1)->roles()->get();
foreach ($roles as $role) {
echo $role->name . "<br>";
}
echo " ******** After Laravel sort by pivot table (Method 1) ******** <br>";
$sorted_roles = User::find(1)->roles()->orderBy('role_user.role_id', 'desc')->get();
foreach ($sorted_roles as $role) {
echo $role->name . "<br>";
}
});
We get the roles of the first user. We then sort the roles in descending according to the pivot table's role_id property.
You can also sort the roles while defining the relationship in the User model.
public function roles() {
return $this->belongsToMany(Role::class, '<pivot_table>')->withPivot('<pivot_table column>')->orderBy('<pivot_table column>');
}
Method 2: Use orderByPivot() method
You can also sort by a pivot table using the orderByPivot() method.
<?php
use App\Models\User;
use Illuminate\Support\Facades\Route;
Route::get('/', function () {
echo " ******** Before Laravel sort by pivot table ******** <br>";
$roles = User::find(1)->roles()->get();
foreach ($roles as $role) {
echo $role->name . "<br>";
}
echo " ******** After Laravel sort by pivot table (Method 2) ******** <br>";
$sorted_roles = User::find(1)->roles()->orderByPivot('id', 'desc')->get();
foreach ($sorted_roles as $role) {
echo $role->name . "<br>";
}
});
Using the orderByPivot(), we get the first user's roles and sort the roles using the pivot table's id column. You can replace the id column with a column of your choice, for example. created_at, range, or role_id.
![Laravel sort by Pivot Table [5 Methods]](https://www.golinuxcloud.com/wp-content/uploads/use-orderByPivot-method.jpg "Laravel sort by Pivot Table [5 Methods]")
Method 3: Use latestPivot() method
You retrieve the roles for the user with ID 1, this time sorted by the latest pivot in the pivot table using latestPivot('id'). This sorts the roles based on the 'id' field in the pivot table, in descending order. You then iterate over the $latest_roles collection and print the name of each role, preceded by a message indicating that these are the roles after sorting by the latest pivot.
<?php
use App\Models\User;
use Illuminate\Support\Facades\Route;
Route::get('/', function () {
echo " ******** Before Laravel sort by pivot table ******** <br>";
$roles = User::find(1)->roles()->get();
foreach ($roles as $role) {
echo $role->name . "<br>";
}
echo " ******** After Laravel sort by latest pivot ******** <br>";
$latest_roles = User::find(1)->roles()->latestPivot('id')->get();
foreach ($latest_roles as $role) {
echo $role->name . "<br>";
}
});
Method 4: Using oldestPivot() Method
You retrieve the roles for the user with ID 1, this time sorted by the oldest pivot in the pivot table using oldestPivot('id'). This sorts the roles based on the 'id' field in the pivot table, in ascending order. You then iterate over the $oldest_roles collection and print the name of each role, preceded by a message indicating that these are the roles after sorting by the oldest pivot.
<?php
use App\Models\User;
use Illuminate\Support\Facades\Route;
Route::get('/', function () {
echo " ******** Before Laravel sort by pivot table ******** <br>";
$roles = User::find(1)->roles()->get();
foreach ($roles as $role) {
echo $role->name . "<br>";
}
echo " ******** After Laravel sort by oldest pivot ******** <br>";
$oldest_roles = User::find(1)->roles()->oldestPivot('id')->get();
foreach ($oldest_roles as $role) {
echo $role->name . "<br>";
}
});
Conclusion
Laravel's Eloquent ORM provides an easy way to manage many-to-many relationships between models, such as users and roles. To store these relationships, Laravel uses an intermediary table called a pivot table, which connects the related models. When querying these relationships, you might want to sort the results based on the data in the pivot table. Laravel offers several methods to achieve this, making it simple to query and sort data based on pivot table values.
Here are some of the most common methods for sorting by pivot table in Laravel:
orderByPivot(): This method allows you to sort the results based on a column in the pivot table in ascending or descending order. For example,User::find(1)->roles()->orderByPivot('created_at', 'desc')->get();sorts the user's roles based on the 'created_at' column in descending order.latestPivot(): This method sorts the related models based on the latest value of a specified pivot table column, typically in descending order. For example,User::find(1)->roles()->latestPivot('created_at')->get();sorts the user's roles by the 'created_at' column with the latest entries first.oldestPivot(): This method sorts the related models based on the oldest value of a specified pivot table column, typically in ascending order. For example,User::find(1)->roles()->oldestPivot('created_at')->get();sorts the user's roles by the 'created_at' column with the oldest entries first.
These methods make it easy to sort related models in Laravel based on pivot table values. Remember that these methods assume you have set up your models and relationships correctly, with the appropriate many-to-many relationship defined and the necessary pivot table in place.
