Eloquent is a powerful ORM (Object-Relational Mapping) tool that allows developers to interact with databases in a more convenient and elegant way. One of the most powerful features of Eloquent is its ability to handle relationships between tables. In this article, we will explore how to order Eloquent’s hasMany relationship by a specific column in the related table.
Let’s take a two-level relationship example to illustrate this. Consider a scenario where you have a User model that has many Posts and each Post has many Comments. Now you want to load the users with their posts and comments, but comments should be ordered by the number of likes, the DB field comments.likes_count.
Here is the model structure for this example:
app/Models/User.php:
class User extends Authenticatable
{
public function posts()
{
return $this->hasMany(Post::class);
}
}
app/Models/User.php:
class User extends Authenticatable
{
public function posts()
{
return $this->hasMany(Post::class);
}
}
app/Http/Controllers/Api/UserController.php:
class UserController extends Controller
{
public function index()
{
return User::with('posts.comments')->get();
}
}
Let’s say that we have seeded the DB with 1 user, 1 post, and 2 comments. The resulting JSON will be like this:
[
{
"id": 1,
"name": "Prof. Chanler Bing",
"email": "chanler.bing@example.com",
"posts": [
{
"title": "Example Post 1",
"comments": [
{
"id": 1,
"comment_text": "MyFirst comment",
"likes_count": 1,
"created_at": "2023-01-20T19:12:45.000000Z",
},
{
"id": 2,
"comment_text": "Second comment",
"likes_count": 3,
"created_at": "2023-01-20T19:12:45.000000Z",
}
]
}
]
}
]
As you can see, the comments are ordered by their id, but we want to order them by likes_count. How to do that?
There are three ways to order by a specific column in Eloquent’s hasMany relationship.
Table of Contents
Option 1: Callback Function with orderBy
When loading the relationship, you can pass it as an array of key-value, where the key is the relationship name and the value is a callback function with extra conditions, like orderBy or whatever you want.
class UserController extends Controller
{
public function index()
{
return User::with(['posts.comments' => function($query) {
$query->orderBy('comments.likes_count', 'desc');
}])->get();
}
}
The key of the array is ‘posts.comments’, which means that we are loading the comments relationship for the posts relationship of the User model. The value of the array is a callback function, where we are passing a $query variable that is an instance of the Eloquent query builder. In this callback function, we are using the orderBy method of the query builder, which takes two parameters: the first one is the column name that we want to sort by, and the second one is the sorting direction (asc or desc). In this case, we are sorting by the ‘likes_count’ column in descending order.
This way, when we call the index method of the UserController, we are returning all the users with their posts and comments, but the comments are sorted by the ‘likes_count’ column in descending order.
It is important to note that this method will only sort the comments for the specific query, it will not change the default order of comments in other parts of the application.
This is a great way to sort the relationship data when you need it only for a specific query and don’t want to change the default ordering for other queries.
Option 2. Model: ALWAYS Order By Field X
Maybe you want that relationship to always be ordered by that field?
You can do this:
app/Models/Post.php:
class Post extends Model
{
public function comments()
{
return $this->hasMany(Comment::class)->orderBy('likes_count', 'desc');
}
}Then, in the Controller, you just leave it as it was, User::with(‘posts.comments’)->get() and the results would be ordered correctly, anyway.
This option is useful when you always want the related data to be ordered by a certain field, regardless of the controller or action that loads the data. This way, you don’t have to worry about adding the orderBy condition every time you load the data, and you can centralize the ordering logic in the model.
However, keep in mind that this option does not allow for dynamic ordering, meaning you can’t change the order of the related data based on the user’s request or other external factors. If you need this kind of flexibility, then the callback function with orderBy option is a better choice.
Option 3. Using the eagerLoad method
Another way to sort the related models is by using the eagerLoad method. This method allows you to load the relationship and sort it at the same time, without having to use a callback function or modify the model.
Here’s an example of how you would use the eagerLoad method to sort the comments by likes_count:
class UserController extends Controller
{
public function index()
{
return User::with(['posts.comments' => function($query) {
$query->orderBy('likes_count', 'desc');
}])->get();
}
}As you can see, the eagerLoad method is similar to the callback function in Option 1, but it allows you to directly specify the sort order without having to use the orderBy method.
Summary:
In conclusion, there are multiple ways to sort the related models in a hasMany relationship in Eloquent. You can use a callback function with the orderBy method, modify the model to always sort by a specific field, or use the eagerLoad method. Each approach has its own advantages and disadvantages, so it’s important to consider which one is the best fit for your specific use case.
