Order By before Group By using Eloquent (Laravel)

Order By before Group By using Eloquent (Laravel)

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

Stefan Bogdanescu

Founder & Senior Architect

Why orderBy Before groupBy Fails in Eloquent

You are encountering a fundamental misunderstanding of how SQL aggregation works, and consequently, why your current Eloquent query returns the wrong data. The core issue isn't that you need to "order by before grouping" as a special feature; it is that standard SQL does not support ordering rows prior to aggregating them in a way that affects which row's value is selected for the group.

When you execute groupBy('fromId'), the database engine scans the entire dataset, partitions it into buckets based on the fromId column, and then applies aggregate functions (like MAX, MIN, or COUNT) to those partitions. Standard SQL does not allow you to say "sort these rows first, pick one from each group, then sort again." If you simply chain ->orderBy('createdAt')->groupBy('fromId'), the database ignores the order for the selection logic unless you explicitly tell it how to pick a single row from that partition.

The Missing Aggregate Function

To get the latest message from each conversation (where toId matches your user and you group by fromId), you must instruct the database which value to retain within each group. Since you want the latest message, you need to select the maximum timestamp associated with that sender.

In pure SQL, this looks like selecting the message content alongside a conditional check or an aggregate function. However, Eloquent simplifies this pattern using the orderBy method combined with a specific selection strategy, but your current approach of chaining groupBy directly on the model builder without handling the "which row" logic is where it breaks.

The Correct Approach: Selecting the Max Timestamp

The most robust way to solve this in Eloquent is to select the maximum createdAt timestamp for each group and then retrieve that specific row. You can achieve this by ordering your results descending by createdAt after the query is constructed, but you must ensure the database knows to return the latest entry.

Since groupBy collapses multiple rows into one per fromId, you must explicitly tell Eloquent which column value to keep. In MySQL (which uses the latin1 charset in your schema), you can use a conditional aggregation or simply rely on the fact that if you order by createdAt descending before the group by, some databases will pick the first available row. However, relying on undefined behavior is dangerous.

A safer, more explicit method involves selecting the maximum timestamp and joining back to get the message content:

$chats = Message::select('fromId', 'message')
    ->joinSub(QueryBuilder::table('messages')->where('toId', $id)
        ->orderByDesc('createdAt'), 'latest_message', function ($q) {
            $q->select('createdAt', 'fromId', 'message');
        })
    ->groupBy('fromId')
    ->paginate(10);

Wait, that subquery approach is overly complex for a simple "get latest" scenario. Let's stick to the standard SQL pattern that works reliably across MySQL versions: use GROUP_CONCAT if you just need IDs, or better yet, select the max timestamp and join.

Actually, the cleanest Eloquent solution for "latest message per sender" is to leverage the fact that we want the row with the highest createdAt. If you group by fromId, you lose the specific row context unless you define an aggregate.

Here is the corrected pattern using a raw query logic translated into Eloquent:

$chats = Message::select('fromId', 'message')
    ->where('toId', $id)
    ->orderByDesc('createdAt')
    ->groupBy('fromId')
    ->paginate(10);

Does this work? In MySQL, if you run an ORDER BY before a GROUP BY, the SQL standard says the order is undefined. However, many drivers and specific MySQL configurations might return the first row encountered in the sort order, which happens to be the latest. But this is not guaranteed behavior across all SQL dialects or database versions.

The Robust Solution: Using max on Timestamp

To guarantee you get the latest message without relying on undefined ordering behavior, you should explicitly select the maximum timestamp and then retrieve the corresponding message. However, since your table structure has a single createdAt column per row, you cannot simply groupBy('fromId') and expect to keep the message column unless you use an aggregate like MAX(message), which doesn't make sense for text data.

The industry-standard workaround is to select the maximum createdAt and then join back to the original table to fetch the full message object, or simply accept that in MySQL 8.0+, window functions provide a cleaner solution without complex joins.

If you are on a version of Laravel and MySQL that supports window functions (MySQL 8.0+), you can use orderBy and limit logic differently, but Eloquent doesn't natively support window functions like ROW_NUMBER() out of the box in a simple query builder chain.

The most reliable method without upgrading database versions or rewriting to raw queries is to select the maximum timestamp as a pivot column and then filter:

$latestMessages = DB::table('messages')
    ->where('toId', $id)
    ->select('fromId', 'createdAt', 'message')
    ->orderByDesc('createdAt')
    ->limit(10) // This gets the 10 most recent overall, not per group.

This fails the grouping requirement. To get one row per fromId with the latest message, you must use a subquery or a window function logic.

Recommended Implementation

The best practice here is to select the maximum timestamp and join:

$chats = DB::table('messages')
    ->select('m.fromId', 'm.message', 'm.createdAt as maxCreatedAt')
    ->join(DB::raw('(SELECT fromId, createdAt, message FROM messages WHERE toId = ? ORDER BY createdAt DESC LIMIT 1) as latest'), 
           'latest', function ($join) use ($id) {
               $join->on('m.fromId', '=', 'latest.fromId')
                    ->where('m.createdAt', '=', 'latest.createdAt');
           }, $id)
    ->orderByDesc('maxCreatedAt')
    ->paginate(10);

This is verbose. A simpler, more modern approach if you are on MySQL 8.0+ is to use a window function:

$chats = Message::selectRaw('fromId, message, createdAt')
    ->where('toId', $id)
    ->addSelect(DB::raw('ROW_NUMBER() OVER (PARTITION BY fromId ORDER BY createdAt DESC) as rn'))
    ->whereColumn('rn', '=', 1) // Filter to keep only the first row per group
    ->paginate(10);

If you cannot use window functions, stick to selecting the max timestamp and joining. The key takeaway is that groupBy alone does not preserve non-aggregated columns like message; you must either aggregate them (which is wrong for text) or use a join/subquery strategy to retrieve the specific row corresponding to the maximum timestamp.

For more advanced query building patterns, check out resources from LaravelCompany which often cover nuanced database optimizations.

Our network