laravel eloquent sort by relationship

laravel eloquent sort by relationship

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Sorting BelongsToMany Relationships by Nested Child Data

When you need to order a belongsToMany relationship based on data from a nested child model, standard Laravel Eloquent patterns often fail because they don't account for the complexity of joining multiple tables and maintaining correct groupings. If you are trying to sort a user's channels by the latest reply in each channel, simply appending an orderBy clause inside your local scope or eager loading definition won't work as expected. The query builder gets confused about which table to order against when aggregating data across multiple relationships.

In your specific scenario, the core issue lies in how Eloquent handles eager loading with nested scopes versus raw SQL joins. When you attempt ->with(['replies'])->orderBy('replies.created_at'), Eloquent tries to apply that ordering to the local query scope of the relationship itself. Since the channels relationship is a many-to-many pivot, adding an order by clause there doesn't inherently link to the replies table without an explicit join.

You correctly identified that using a join inside the relationship definition allows you to fetch the data. However, your working solution introduced a critical side effect: channels without replies were excluded from the result set entirely. This happens because a left join or inner join filters out rows where the replies table has no matching record for a specific channel. If a user belongs to Channel A and Channel B, but Channel B has zero replies, an inner join will drop Channel B from the final list before you can group it by its ID.

To solve this while preserving channels with no activity, you must use a leftJoin. This ensures that every channel associated with the user is included in the result set, regardless of whether a matching reply exists. The challenge then shifts to ordering: if Channel B has no replies, what value should determine its position in the list? In a chat application context, it usually makes sense to push silent channels to the bottom or top, depending on your UX requirements.

Here is how you can implement this robustly using Eloquent's withCount and orderByRaw, or by leveraging a raw join with careful grouping. You cannot rely on standard scopes like latest() inside the eager loading callback to influence the primary query's ordering of the parent collection. Instead, you must construct a custom scope that joins the necessary tables.

Consider this approach for the User model:

public function activeChannels()
{
    return $this->belongsToMany(Channel::class, 'channel_user')
                ->withPivot('is_approved')
                ->withCount(['replies' => function ($query) {
                    $query->selectRaw('count(*) as count');
                }]);
}

While withCount helps you filter or sort by activity level, it doesn't give you the specific timestamp of the latest reply for ordering purposes. For precise chronological sorting where you need to distinguish between "empty" and "active," a raw join is often necessary. You can define a relationship scope that joins the replies table left-side:

public function channelsWithReplies()
{
    return $this->belongsToMany(Channel::class, 'channel_user')
                ->withPivot('is_approved')
                ->join('replies', function ($join) {
                    $join->on('channels.id', '=', 'replies.channel_id')
                         ->whereNull('replies.user_id'); // Optional: filter out your own replies if needed
                })
                ->orderByRaw('replies.created_at is null desc, replies.created_at desc')
                ->groupBy('channels.id', 'channels.name', 'pivot.is_approved');
}

The orderByRaw clause handles the logic of pushing channels without replies to the bottom (or top) by checking for NULL values first. The groupBy is essential here because you are selecting columns from the joined replies table alongside the main pivot and channel tables; without grouping, the query builder will throw an error regarding non-aggregated columns in a GROUP BY statement.

One pitfall to watch out for is performance. Joining directly into a relationship definition can lead to N+1 query problems if not managed correctly, especially when filtering by is_approved. If you need to filter approved channels and sort them by reply date, ensure your belongsToMany accepts the pivot constraint properly so Eloquent generates the correct SQL structure.

If you prefer sticking strictly to Eloquent scopes without raw joins, you might consider using a separate query that groups by channel ID and orders by MAX(created_at). However, this requires manual collection processing after retrieval, which deviates from the eager-loading pattern you originally sought. The join method remains the most direct way to achieve sorting by nested child data while keeping the logic contained within the model relationship definition.

For advanced cases involving complex ordering across multiple levels of relationships, checking resources like https://laravelcompany.com can provide additional context on handling intricate query structures in production environments.

Our network