Laravel count, groupBy and orderBy not working properly

Question

This is the structure of my table [1]: https://i.stack.imgur.com/lmgtJ.png

Users Table

id	name
1	abid

Messages Table

id	user_id	to_user	content	read
1	2	1	message	0

I want to show notifications for users like User1 send you messages (24) here 24 is the total unread messages of user1.

Problem: Notification must be shown in this order. The user that sends messages recently will show first. It is exactly the problem that I have to face.

Output Example: like the android app WhatsApp show a message in the chat area.

Note: read column type Boolean also added in the messages table.

Note: In this application only users and admin will chat with each other.

Looking for elegant Laravel 8 queries that give me results.

Answer 1

$message = DB::table('message')->select('message.user_id');

/* join */
$message = $message->join('user', 'message.to_user', '=', 'user.id');    

/* select statements */
$message = $message->addSelect('message.to_user');
$message = $message->addSelect(DB::raw('COUNT(*) AS total_messages'));

$message = $message->groupBy('message.user_id', 'message.to_user');
$message = $message->get();

This should count how many messages that user sent to another user(s).

Demo: SQL Fiddle

Answer 2

Solution.

Finally, I have found a solution by review these links I want to appreciate those people by giving links to their answers.

1. GroupBy after orderBy for complicated query in Laravel

2. Order By before Group By using Eloquent (Laravel)

3. SQL select only rows with max value on a column

Here is the final code

$result= Message::with('users')
         ->select(DB::raw('id,from_user,to_user,created_at, max(created_at) as createdAt'))
         ->where('to_user',$user)
         ->orderBy('createdAt', 'desc')
         ->groupBy('from_user')
         ->selectRaw('count(messages.from_user) as total_msg')
         ->get();

And here is the final result

Final Output