Sorting Laravel Eloquent Results by Many-to-One Relationships

As a Laravel developer, sorting database results is second nature. But what about when you need to sort by a field in a many-to-one relationship? Let's say you have posts, and each post belongs to a status (which is its own model). How do you sort posts by the status name?

You could load everything and sort the collection in memory, that works fine for small datasets. But when you're dealing with thousands of records, you'll quickly eat up your RAM. Let the database handle it instead! In this post, I'll show you how to sort by many-to-one relationships directly in your Eloquent queries.

The scenario

Imagine you have these models with a many-to-one relationship (using Laravel's belongsTo):

class Post extends Model
{
    public function status()
    {
        return $this->belongsTo(Status::class);
    }
}

class Status extends Model
{
    // Has 'name' field
}

You want to fetch all posts sorted by their status name. Here's how to do it.

Option 1: Using a join with hardcoded table names

The most common approach is to join the related table:

Post::query()
    ->join('statuses', 'posts.status_id', '=', 'statuses.id')
    ->orderBy('statuses.name')
    ->select('posts.*')
    ->get();

Pros:

• Simple and easy to understand
• Best performance: direct join at the database level
• Works for basic cases

Cons:

• Hardcoded table and column names break Eloquent conventions
• If you change your table names, you'll need to update these strings
• Bypasses the relationship definition you already have
• That select('posts.*') is easy to forget and causes column bleeding

This works and performs well, but it's tempting to drop down to the database layer like this. You lose the benefits of Eloquent's abstractions and your code becomes tightly coupled to your database schema.

Option 2: Using a join with the relationship

A slightly cleaner approach that leverages the many-to-one relationship and Eloquent's helper methods:

$relation = (new Post)->status();

Post::query()
    ->join(
        $relation->getRelated()->getTable(),
        $relation->getQualifiedForeignKeyName(),
        '=',
        $relation->getRelated()->getQualifiedKeyName()
    )
    ->orderBy($relation->getRelated()->qualifyColumn('name'))
    ->select('posts.*')
    ->get();

In terms of maintainability, this is better. Now you're actually using the relationship you've defined. But that verbosity makes it a perfect candidate for a scope:

public function scopeOrderByStatus($query, $direction = 'asc')
{
    $relation = $this->status();
    
    return $query
        ->join(
            $relation->getRelated()->getTable(),
            $relation->getQualifiedForeignKeyName(),
            '=',
            $relation->getRelated()->getQualifiedKeyName()
        )
        ->orderBy($relation->getRelated()->qualifyColumn('name'), $direction)
        ->select('posts.*');
}

Now you get the best of both worlds: clean usage with proper relationship handling:

Post::orderByStatus()->get();
Post::orderByStatus('desc')->get();

Pros:

• Uses the actual relationship definition, no manual foreign key references
• Automatically adapts to any relationship configuration changes
• Uses Eloquent's built-in methods for column qualification
• Still performant with a proper join

Cons:

• More verbose when used inline
• Still requires that select('posts.*') to avoid column conflicts
• Instantiating models just to get relationship info feels a bit awkward

Option 3: Using a subquery

If you want to stay fully in the Eloquent layer without joins, you can use a subquery:

Post::query()
    ->orderBy(
        Status::select('name')
            ->whereColumn('statuses.id', 'posts.status_id')
            ->limit(1)
    )
    ->get();

This is more shorter and, to my opinion, much more readable.

A more maintainable approach that leverages the many-to-one relationship and Eloquent's helper methods:

$relation = (new Post)->status();

Post::query()
    ->orderBy(
        $relation->getRelated()::select('name')
            ->whereColumn(
                $relation->getRelated()->getQualifiedKeyName(),
                $relation->getQualifiedForeignKeyName()
            )
            ->limit(1)
    )
    ->get();

Like option 2, it's perfect for extracting into a scope:

public function scopeOrderByStatus($query, $direction = 'asc')
{
    $relation = $this->status();
    
    return $query->orderBy(
        $relation->getRelated()::select('name')
            ->whereColumn(
                $relation->getRelated()->getQualifiedKeyName(),
                $relation->getQualifiedForeignKeyName()
            )
            ->limit(1),
        $direction
    );
}

Pros:

• Stays within Eloquent's abstraction layer
• No select('posts.*') required
• Avoids column name conflicts entirely (because it's a subquery)
• Automatically adapts to relationship configuration changes

Cons:

• Less performant than a join when using large datasets
• Can be confusing for developers unfamiliar with subquery ordering
• Quite verbose when used inline
• The 'name' column is still hardcoded

Here's the tradeoff: this approach is cleaner from a code perspective, but databases tend to be slower with handling subqueries. For small to medium datasets, the performance difference is negligible. For large datasets with thousands of records, the join approach (option 2) will probably be faster.

Which one should you use?

You've essentially got two approaches: joins or subqueries. Both can be wrapped in scopes for reusability.

• Use option 1 (hardcoded join) when:
  • You're working on a quick prototype or proof of concept
  • The table structure is stable and unlikely to change
  • You need maximum performance and minimal overhead
  • You're comfortable with the maintenance tradeoff
• Use option 2 (join using relationship) when:
  • Performance is critical and you're dealing with large datasets
  • You prefer explicit joins in your SQL
  • You want the absolute best query performance
  • You want to fully leverage your relationship definitions
• Use option 3 (subquery) when:
  • You want cleaner code without worrying about select('posts.*')
  • You're working with normal-sized datasets where the performance difference is negligible
  • You want to avoid potential column conflicts
  • You prioritize code readability over micro-optimizations

The takeaway

When it comes to sorting by relationships, you've got options. You can go with hardcoded joins, leverage Eloquent's relationship methods, or use subqueries. Each has its place depending on your needs.

Here's the reality: it's really tempting to just drop down to option 1 with hardcoded table names. It's quick, it works, and it performs well. But you're trading short-term convenience for long-term maintainability. When your database schema changes, you'll be hunting through your codebase for all those hardcoded strings. Your future self (and your teammates) will thank you when the codebase evolves.

There's a tradeoff between cleaner code and better database performance. Subqueries keep your code in Eloquent's layer but add overhead in many cases. Joins are generally faster but require more careful handling. Pick the approach that fits your situation, wrap it in a scope for reusability, and you're good to go.