Get most recent row with group by and Laravel

Question

Even though there are multiple questions like this I can't get my query to return the row with the most recent date with a group by.

I have the following table..

| message_id | from | to | created_at | status
----------------------------------------------
|    1       |   1  |  2 | 2017-04-06 |   1
|    2       |   1  |  2 | 2017-04-07 |   0
|    3       |   3  |  4 | 2017-04-06 |   1
|    4       |   3  |  4 | 2017-04-07 |   0
----------------------------------------------

and I'm tryin to get the rows with most recent date.

| message_id | from | to | created_at | status
----------------------------------------------
|    2       |   1  |  2 | 2017-04-07 |   0
|    4       |   3  |  4 | 2017-04-07 |   0

Currently this query returns the rows with the last recent date.

$messages = Message::where('to', Auth::id())
                    ->groupBy('from')
                    ->orderBy('created_at', 'DESC')
                    ->paginate(10);

Answer 1

The problem is that the result set will be first grouped then ordered. You can use nested select to get what you want.

SQL Query:

SELECT t.* FROM (SELECT * FROM messages ORDER BY created_at DESC) t GROUP BY t.from

With Laravel:

$messages = Message::select(DB::raw('t.*'))
            ->from(DB::raw('(SELECT * FROM messages ORDER BY created_at DESC) t'))
            ->groupBy('t.from')
            ->get();

You just need to add your where() clauses.

Answer 2

To get most recent record for each from you can use a self join

DB::table('message as m')
  ->select('m.*')
  ->leftJoin('message as m1', function ($join) {
        $join->on('m.from', '=', 'm1.from')
             ->whereRaw(DB::raw('m.created_at < m1.created_at'));
   })
  ->whereNull('m1.from')
  ->orderBy('m.created_at', 'DESC')
  ->paginate(10);

In SQL it will look like

select m.*
from message m
left join message m1 on m.from = m1.from
and m.created_at < m1.created_at
where m1.from is null
order by m.created_at desc

Laravel Eloquent select all rows with max created_at

Answer 3

If anyone is still looking for a short and Eloquent answer for using groupBy() with latest() just add unique() after get() like this...

$unreadMessages = Message::where('receiver_id', Auth::user()->id)
    ->where('read_at', null)
    ->latest()
    ->get()
    ->unique('user_id');

--- Update ---

Problem with the above solution is that it uses unique() method on the result set, which is not efficient because it operates on the entire result set after it has been retrieved from the database. This can be slow and resource-intensive, especially when dealing with large datasets.

• Solution 1

  $unreadMessages = Message::where('receiver_id', Auth::user()->id) ->where('read_at', null) ->latest('created_at') ->groupBy('user_id') ->get();

Here, we are using groupBy() method instead of using unique() on the result set. The groupBy() is applied on the database level, which can be much more efficient.

• Solution 2

  $unreadMessages = Message::select('user_id', DB::raw('max(created_at) as max_created_at'), DB::raw('*')) ->where('receiver_id', Auth::user()->id) ->where('read_at', null) ->groupBy('user_id') ->get();

In this query, we are selecting the user_id, max(created_at) as max_created_at, and * which will give all the columns.

By using groupBy('user_id') will only select the distinct user_id, and using max(created_at) as max_created_at it will give you the latest message per user. This way, you can retrieve the latest unread messages per user with a single query to the database.

Answer 4

You may replace groupBy with distinct, as it works in my case.

$messages = Message::where('to', Auth::id())
                ->orderBy('created_at', 'DESC')
                ->distinct('from')
                ->paginate(10);

Hope this helps.

Answer 5

You've got a date field in your example, not a datetime field, so the following is my preferred method:

# Mysql
select * from
  your_table
where date_field = (select max(date_field) from your_table)

// Laravel
YourModel:::query()->whereRaw('date_field = (select max(date_field) from your_table)')->get();

• Note the above wont work as expected for datetime fields because the rows will have different timestamps, it'll likely just return the single latest row.

Answer 6

You might also want to orderBy message_id as well

$messages = Message::where('to', Auth::id())
                    ->groupBy('from')
                    ->orderBy('created_at', 'DESC')
                    ->orderBy('message_id', 'DESC')
                    ->paginate(10);