Laravel Eloquent Query group by user and order by Max(date)

Question

I have the following table. I am actually designing a simple chat, so, we have the user_id (the person who sends the message) and user_id_to (the person who receives the message).

id	user_id	user_id_to	message	send_date
1	1	2	test 1	01-2-2023
2	2	1	test 2	02-2-2023
3	3	1	test 3	03-2-2023
4	1	2	test 4	05-3-2023
5	2	1	test 5	08-3-2023
6	3	1	test 6	10-3-2023
7	4	1	test 7	11-3-2023
8	5	1	test 8	13-3-2023
9	5	1	test 9	15-3-2023
10	5	1	test 10	20-3-2023
11	1	3	test 11	22-3-2023

I want to be able to select the distinct user (grouped by user), and message, order by max send_date.

So, with the above data, the following should be the result.

id	user_id	user_id_to	message	send_date
11	1	3	test 11	22-3-2023
10	5	1	test 10	20-3-2023
7	4	1	test 7	11-3-2023
6	3	1	test 6	10-3-2023
5	2	1	test 5	08-3-2023

I need to use Laravel Eloquent, because with that I can use with('user', 'user_to') to select the users data together.

Answer provided by Karl Hill works, but it didn't select the message column. How can I also select the message column?

Please help.

Answer 1

I am assuming that you want the latest chat for each distinct pair of users. I am also assuming that all chat between (1, 2) and (2, 1) is considered one conversation.

To get the top-1-per-group you can use a not exists query with a twist:

select *
from chat as newer
where (
    newer.user_id = ? or
    newer.user_id_to = ?
) and not exists (
    select 1
    from chat as older
    where (
        -- the (user_id, user_id_to) pair needs to match the corresponding
        -- pair from outer query in same order or reversed
        older.user_id = newer.user_id and older.user_id_to = newer.user_id_to or
        older.user_id = newer.user_id_to and older.user_id_to = newer.user_id
    )
    and older.send_date > newer.send_date
)

Demo on DB<>Fiddle
The fiddle also contains a row_number solution if your RDBMS supports it.

The eloquent code to produce the above query would be:

$some_user_id = 1;
$query = DB::table('chat as newer')
            ->where(function ($query) use ($some_user_id) {
                $query->where('newer.user_id', '=', $some_user_id)
                    ->orWhere('newer.user_id_to', '=', $some_user_id);
            })
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                ->from('chat as older')
                ->where(function ($query) {
                    $query->whereColumn('older.user_id', '=', 'newer.user_id')
                        ->whereColumn('older.user_id_to', '=', 'newer.user_id_to')
                        ->orWhereColumn('older.user_id', '=', 'newer.user_id_to')
                        ->whereColumn('older.user_id_to', '=', 'newer.user_id');
                })
                ->whereColumn('older.send_date', '>', 'newer.send_date');
            });

Answer 2

You can use Eloquent's groupBy, orderBy, and with methods. Convert the send_date to a date format that MySQL can understand. You can do this by adding send_date to your model's $dates property.

protected $dates = ['send_date'];

Then, use the DB::raw function to select the maximum send_date for each user_id. You can then group by user_id and order by the maximum send_date.

use Illuminate\Support\Facades\DB;

$chats = Chat::select(DB::raw('max(id) as id, user_id, user_id_to, max(send_date) as send_date'))
    ->groupBy('user_id')
    ->orderBy('send_date', 'desc')
    ->with('user', 'user_to')
    ->get();

This query will return the latest chat for each user, not the latest chat for each pair of users. If you want to get the latest chat for each pair of users, you should group by both user_id and user_id_to.

Answer 3

First, let's see this with a raw SQL query:

select latest.id, latest.user_id, latest.user_id_to, latest.message, latest.send_date
from yourtable latest
left join yourtable followup
on latest.user_id = followup.user_id and latest.user_id_to = followup.user_id_to and latest.send_date < followup.send_date
where followup.id is null

Here, we find the latest record by left joining latest to followup based on user_id and user_id_to matching as well as followup being later than latest. In the where clause we ensure that only those latest records are selected that have no followup matches, that is, they are indeed the latest.

Now, let's convert this into an Eloquent query:

YourTable::DB::table('yourtable as latest')
    ->select(DB::raw("select latest.id, latest.user_id, latest.user_id_to, latest.message, latest.send_date"))
    ->leftJoin('yourtable as followup', function($join) {
          $join->on('latest.user_id', '=', 'followup.user_id');
          $join->on('latest.user_id_to', '=', 'followup.user_id');
          $join->on('latest.send_date', '<', 'followup.send_date');
      })
     ->where(DB::raw('followup.id is null'));

Answer 4

I am assuming that your Modal name is Message

$latestMessages = Message::select('user_id', \DB::raw('MAX(send_date) as latest_date'))
                          ->groupBy('user_id');

$messages = Message::joinSub($latestMessages, 'latest_messages', function ($join) {
                            $join->on('messages.user_id', '=', 'latest_messages.user_id')
                                 ->on('messages.send_date', '=', 'latest_messages.latest_date');
                        })
                        ->with('user', 'user_to')
                        ->orderBy('send_date', 'desc')
                        ->get(['messages.*']);

Echo the $messages variable to see the results. This also satisfy your requirement of using:

  with('user', 'user_to');

to select the user data together with the messages.