Laravel Question posted in
The official Laravel Documentation can be found here.

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

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
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.

Tags:

2

Answers


  1. 0

    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 IlluminateSupportFacadesDB;

$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.

    Login or Signup to reply.
  2. 0

    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'));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search