Ordering database queries by relationship columns in Laravel

In this article we're going to explore how to order database queries by the value (column) of an Eloquent relationship. For example, maybe we want to order some users by the name of their company, which is in a separate companies table.

The way to do this is different depending on the relationship type. However, it always involves ordering by a column in a separate database table, and that's what makes it somewhat tricky, especially compared to a normal order by. And yet, this is a common enough thing to want to do!

This article will cover the following relationship types:

• Has-one relationships
• Belongs-to relationships
• Has-many relationships
• Belongs-to-many relationships

Ordering a relationship

To be clear, what we're trying to do here is order an Eloquent model database query by the value of one of its relationships. We're not trying to simply order the results of the relationship itself. In fact, you might want to a order database query by a relationship value without even loading that relationship from the database!

However, just in case you landed on this article wondering how to order an Eloquent relationship, here's three techniques you can use.

First, you can simply append an order by statement to your relationship:

class Company extends Model
{
    public function users()
    {
        return $this->hasMany(User::class)->orderBy('name');
    }
}

Now, whenever you call $company->users (as a collection), or $company->users() (as a query builder), the users will be automatically ordered by their name.

The second way is by eager loading the relationship with an order by statement.

class CompaniesController extends Controller
{
    public function show(Company $company)
    {
        $company->load(['users' => function ($query) {
            $query->orderBy('name')
        }];

        return View::make('companies.show', ['company' => $company]);
    }
}

This approach gives you more control over how you order your relationship at the controller level. Using this approach lets you order your relationship differently depending on the situation.

Finally, the third way you can order a relationship is by using a global scope on the relationship model itself:

class User extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(fn ($query) => $query->orderBy('name'));
    }
}

Now, whenever you run any User database query, including relationship queries, it will automatically order the results by the user's name.

Okay, enough about ordering relationships themselves. Let's now look at ordering the parent model based on a relationship value! 👇

Ordering by has-one relationships

Consider an app which lists users with their name, email and company. It's currently ordering the users by their name, but what if we wanted to order them by their company instead?

This app includes a User model with a hasOne company relationship. Meaning, the company name exists in the companies table.

class User extends Model
{
    public function company()
    {
        return $this->hasOne(Company::class);
    }
}

There's actually two approaches we can use to order these users by their company. The first is using a join:

$users = User::select('users.*')
    ->join('companies', 'companies.user_id', '=', 'users.id')
    ->orderBy('companies.name')
    ->get();

Let's break this query down.

First we select only the columns from the users table, since, by default, Laravel will select all the columns when using a join, including the columns from the companies table.

User::select('users.*')

Next, we join in the companies table where the company's user_id equals the user's id.

->join('companies', 'companies.user_id', '=', 'users.id')

And finally, we order the records by the company's name column.

->orderBy('companies.name')

Here is the generated SQL for this query:

select users.*
from users
inner join companies on companies.user_id = users.id
order by companies.name asc

The second way is using a subquery. As of Laravel 6, the orderBy() and orderByDesc() query builder methods support passing a query, instead of just a column name. When you do this, the query is executed as a subquery within the order by statement.

$users = User::orderBy(Company::select('name')
    ->whereColumn('companies.user_id', 'users.id')
)->get();

Again, let's break this query down.

First, within the orderBy() method, we pass in a subquery which selects the name from the companies table.

Company::select('name')

Then we limit the results by matching the company's user_id to the user's id.

->whereColumn('companies.user_id', 'users.id')

Here is the generated SQL for this query:

select * from users order by (
    select name
    from companies
    where companies.user_id = users.id
) asc

And while this second approach certainly works, in my experience, for a has-one relationship, this approach is significantly slower than the join approach. Here are some metrics from a demo app I created with 50,000 user records:

So, when ordering has-one relationships, definitely reach for the join approach first.

Ordering by belongs-to relationships

Ordering by a belongs-to relationship is basically exactly the same as ordering by a has-one relationship, except the foreign keys are in opposite tables. To make this article useful from a documentation perspective, this section will be almost entirely duplicated from the has-one instructions. Feel free to skip down to the next section if you're reading this article in its entirety.

Consider an app which lists users with their name, email and company. It's currently ordering the users by their name, but what if we wanted to order them by their company instead?

This app includes a User model with a belongsTo company relationship. Meaning, the company name exists in the companies table.

class User extends Model
{
    public function company()
    {
        return $this->belongsTo(Company::class);
    }
}

Like the has-one relationships, there are two approaches we can use to order these users by their company. The first is using a join:

$users = User::select('users.*')
    ->join('companies', 'companies.id', '=', 'users.company_id')
    ->orderBy('companies.name')
    ->get();

Let's break this query down.

First we select only the columns from the users table, since, by default, Laravel will select all the columns when using a join, including the columns from the companies table.

User::select('users.*')

Next, we join in the companies table where the company's id equals the user's company_id.

->join('companies', 'companies.id', '=', 'users.company_id')

And finally, we order the records by the company's name column.

->orderBy('companies.name')

Here is the generated SQL for this query:

select users.*
from users
inner join companies on companies.id = users.company_id
order by companies.name asc

The second way is using a subquery. As of Laravel 6, the orderBy() and orderByDesc() query builder methods support passing a query, instead of just a column name. When you do this, the query is executed as a subquery within the order by statement.

$users = User::orderBy(Company::select('name')
    ->whereColumn('companies.id', 'users.company_id')
)->get();

Again, let's break this query down.

First, within the orderBy() method, we pass in a subquery which selects the name from the companies table.

Company::select('name')

Then we limit the results by matching the company's id to the user's company_id.

->whereColumn('companies.id', 'users.company_id')

Here is the generated SQL for this query:

select * from users order by (
    select name
    from companies
    where companies.id = users.company_id
) asc

And again, just like the has-one relationship, the subquery approach certainly works, but it's significantly slower than the join approach. Here are some metrics from a demo app I created with 50,000 user records:

So, when ordering belongs-to relationships, definitely reach for the join approach first.

Ordering by has-many relationships

Consider an app which lists users with their name, email and last login date. It's currently ordering the users by their name, but what if we wanted to order them by their last login date instead?

(As an aside, if you're wondering how to get the last login date in the most efficient way, be sure to see my dynamic relationships article.)

This app includes a User model with a hasMany logins relationship. Meaning, the login information exists in a logins table. Each time a user logs in, a new record is created in this table.

class User extends Model
{
    public function logins()
    {
        return $this->hasMany(Login::class);
    }
}

There's actually two approaches we can use to order by has-many relationships. This can be done with a join or with a subquery. Let's start with the subquery approach, since it's simpler.

As of Laravel 6, the orderBy() and orderByDesc() query builder methods support passing a query, instead of just a column name. When you do this, the query is executed as a subquery within the order by statement.

$users = User::orderByDesc(Login::select('created_at')
    ->whereColumn('logins.user_id', 'users.id')
    ->latest()
    ->take(1)
)->get();

Let's take a closer look at this subquery.

First we select the created_at column from the logins table.

Login::select('created_at')

Then we limit the results by comparing the login user_id column to the id of the user in the parent query.

->whereColumn('logins.user_id', 'users.id')

Then we call the latest() method, which orders the logins to get the most recent record first.

->latest()

And finally, we limit the results to only one row, since a subquery can only return a single row and column, but a user will (most likely) have more than one login record.

->take(1)

Here's the generated SQL for this query, which includes our login subquery in the order by statement.

select * from users order by (
    select created_at
    from logins
    where user_id = users.id
    order by created_at desc
    limit 1
) desc

Quite often I'll take this order by statement and create a model scope for it, just to nicely encapsulate it and make it easier to reuse. For example:

public function scopeOrderByLastLogin($query, $direction = 'desc')
{
    $query->orderBy(Login::select('created_at')
        ->whereColumn('logins.user_id', 'users.id')
        ->latest()
        ->take(1),
        $direction
    );
}

And now you can simply call this scope from your controllers (or wherever you need it):

$users = User::orderByLastLogin()->get();

Okay, now let's take a look at the join approach.

$users = User::select('users.*')
    ->join('logins', 'logins.user_id', '=', 'users.id')
    ->groupBy('users.id')
    ->orderByRaw('max(logins.created_at) desc')
    ->get();

Let's break this query down.

First we select only the columns from the users table, since, by default, Laravel will select all the columns when using a join, including the columns from the logins table.

User::select('users.*')

Next, we join in the logins table where the login user_id equals the user's id.

->join('logins', 'logins.user_id', '=', 'users.id')

Next, we group the users by their id, since we only want one row per user.

->groupBy('users.id')

Finally, and this is where things get a little more interesting, we order the records by the "max" login created_at column, descending, to get the users who logged in most recently, first.

->orderByRaw('max(logins.created_at) desc')

Here's the generated SQL for this query:

select users.*
from users
inner join logins on logins.user_id = users.id
group by users.id
order by max(logins.created_at) desc

You might be wondering why we're using themax() aggregate function here. Couldn't we just order by the created_at column? Something like this:

->orderByDesc('logins.created_at')

Well, the short answer is "no".

Without the max() aggregate function, we'll get this syntax error:

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'logins.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What on earth does that mean?

Well, because of our logins join, we're getting multiple records for each user. A row for every login record they have.

Of course, we only want one record per user, which is why we're grouping them by the user id.

However, we're then telling MySQL to order those grouped rows by the login created_at column. But, if there are multiple rows per user, that means we have multiple different login created_at values for each user. How does MySQL know which one of the created_at values to order the user by?

Well, it doesn't. Which is why we get this error.

The important thing to realize here is that, when a query executes, the order by occurs after the group by. That means the order by statement is executed on the grouped rows. And, just like when you do this with a select statement, you must use an aggregate (group by) function to tell MySQL which value you want to use from that grouped set.

And that's why we used the max() aggregate function in our query. It tells MySQL that we want the latest (max) created_at value from the grouped set. And this works for other column types as well. For example, you can use the max() and min() functions to order a string column alphabetically. For a complete list of these aggregate functions, see the MySQL documentation.

Here's a handy reference I use to remember what order queries execute in.

Okay, which of these two approaches is the fastest? The join or the subquery? Here are some metrics from a demo app I created with 50,000 user records:

As you can see, there is no conclusive winner here. So, when ordering by has-many relationships, I'd suggest trying both approaches to see which one works best for your use case.

Also, just a quick note, in order to get these speeds I had to create a compound index on the logins table for the user_id and created_at columns.

Schema::table('logins', function (Blueprint $table) {
    $table->index(['user_id', 'created_at']);
});

Ordering by belongs-to-many relationships

Consider a library app which lists books with the title, author and last checkout user and date. It's currently ordering the books by the title, but what if we wanted to order them by the last checkout date or even the user name instead?

This app includes a Book model with a belongsToMany users relationship that uses a checkouts pivot table. Each time a user checks out a book, a new record is created in this table, which includes the date in the borrowed_date column.

class Book extends Model
{
    public function user()
    {
        return $this->belongsToMany(User::class, 'checkouts')
            ->using(Checkout::class)
            ->withPivot('borrowed_date');
    }
}

class Checkout extends Pivot
{
    protected $table = 'checkouts';

    protected $casts = [
        'borrowed_date' => 'date',
    ];
}

Let's start with ordering by the last borrowed date.

This is actually the easier thing to order by. And that's because the borrowed_date exists on our checkouts pivot table. Checkouts are essentially a has-many relationship to books. One book "has many" checkouts. We just happen to be using the checkouts as the pivot table for the belongs-to-many relationship between books and users.

Meaning, if we want to sort by a column on the checkouts table, we can use the exact same techniques we covered above in the has-many relationships section.

Here's a quick review on how you can do this using a subquery:

$books = Books::orderByDesc(Checkout::select('borrowed_date')
    ->whereColumn('book_id', 'books.id')
    ->latest('borrowed_date')
    ->limit(1)
)->get();

You might be wondering, "what if I am not using a Checkout pivot model, can I still do this?"

Absolutely. The order by methods, as of Laravel 6, also allow you to provide a closure, where you can manually write the subquery.

$books = Books::orderByDesc(function ($query) {
    $query->select('borrowed_date')
        ->from('checkouts')
        ->whereColumn('book_id', 'books.id')
        ->latest('borrowed_date')
        ->limit(1);
})->get();

Here is the generated SQL for both of these queries (they are identical):

select * from books order by (
    select borrowed_date
    from checkouts
    where book_id = books.id
    order by borrowed_date desc
    limit 1
) desc

Okay, now let's move on to actually ordering by a belongs-to-many relationship column.

In our example, that means ordering by a column in the users table. Let's update the books query to order by the name of the user who last checked out that book.

Again, we'll use an order by subquery to do this:

$books = Book::orderBy(User::select('name')
    ->join('checkouts', 'checkouts.user_id', '=', 'users.id')
    ->whereColumn('checkouts.book_id', 'books.id')
    ->latest('checkouts.borrowed_date')
    ->take(1)
)->get();

Let's take a closer look at this subquery.

First we start by selecting the name from the users table, since that's what we want to order by.

User::select('name')

Then we join in the checkouts table where the user_id equals the user's id. We need this join since the checkouts table is what connects the books to the users.

->join('checkouts', 'checkouts.user_id', '=', 'users.id')

Next, we limit the results to only checkouts for this book by comparing the checkout book_id to the book's id.

->whereColumn('checkouts.book_id', 'books.id')

Then we order the checkouts by the borrowed_date column to get the latest one.

->latest('checkouts.borrowed_date')

And finally, we take the first record, since a subquery can only return a single row and column, and, of course, because we only care about the last checkout.

->take(1)

That's all there is to it! Here is the generated SQL for this query:

select * from books order by (
    select name
    from users
    inner join checkouts on checkouts.user_id = users.id
    where checkouts.book_id = books.id
    order by checkouts.borrowed_date desc
    limit 1
) asc

A quick warning about performance. While this technique is great in certain situations, if you're dealing with tens of thousands of records, this approach will likely not be super fast. So, if you run into situations where this technique is causing performance issues, that's probably a good time to introduce some caching.

For example, I'd probably start by adding a last_checkout_id foreign key to the books table as a first step of denormalization.

Schema::table('books', function (Blueprint $table) {
    $table->foreignId('last_checkout_id')->nullable()->constrained('checkouts');
});

That's all for today! I hope you found this article helpful. Be sure to hit me up on Twitter at @reinink if you have any feedback or questions.

Also, just so you know, I cover ordering by all four of these relationships in screencast form in my Eloquent Performance Patterns video course (see below), if you prefer that format. 🤟