Level 56
It is returning only for a single user because of the WHERE clause:
->where('messages.from_id', $request->user_id)
Remove it and you should be fine.
1 like
Feb 6, 2022
9
Level 3
join same table with multiple columns and get the latest record with user data.
i have two tables named users and message, I want to join both tables and get the last message from each user's conversation with user_name. My messages table having columns like from_id, to_id, messages, created_at.
Below is my code.
$chats = DB::table('users')
->leftJoin('messages', function($query)
{
$query->on('users.id', '=', 'messages.to_id')
->whereRaw('messages.id IN (select MAX(messages.id) from messages join users on users.id = messages.to_id group by users.id)');
})
->select('users.id as user_id', 'users.name', DB::raw('CONCAT("https://www.interwebs.co.in/puzzle/attach/", users.avatar) AS image') , 'users.mobile', 'messages.id', 'messages.from_id', 'messages.to_id', 'messages.body as message', 'messages.attachment', 'messages.seen as seen_count', 'messages.created_at')
->where('messages.from_id', $request->user_id)
->get();
My messages table is
| from_id | to_id | message | created_at |
| ---------| --------- | ---------| ------------------ |
| 1 | 2 | abc | 2022-02-04 10:55:34 |
| 2 | 1 | cdef | 2022-02-05 10:56:34 |
| 1 | 3 | defg | 2022-02-06 10:57:34 |
| 1 | 3 | hijk | 2022-02-07 10:58:34 |
| 1 | 2 | lmop | 2022-02-08 10:59:34 |
I have tried this but it is giving me the last message of each conversation of current logged in user and not others.
Level 3
@rodrigo.pedra no this not give me the required output and also not the last message of each user
Level 27
Who or what is user_name? That’s not used anywhere in your code. Are you looking for the last message each user has sent to the currently logged-in user (= $request->user_id)?
At the moment, it looks to me like you’re selecting the last message from the current user to anyone else. If you want messages from each user to the current user, you should change the join between messages and users in the raw subquery to on users.id = messages.from_id (not to_id), and then conversely the main where should be ->where('messages.to_id', $request->user_id). That should do it. I think.
(You’re also not taking into account that “the last message from each user’s conversation” may be either a message to or from the desired user, but that may be intentional – it’s hard to tell from your description).
1 like
Level 3
@kokoshneta thanks for your detailed observation on this question, first of all the username is from users table which is users.name and basically i am trying to get latest message from each conversation from both to_id and from_id. Through which i am trying to build a simple chat application and want to get all conversations with latest message of current logged in user.
Level 27
@Zafeer If a chat application is what you’re after, I think you’d be better off reshaping your database structure for that if you’re still at an early enough stage that it’s feasible, keeping track of chats in a table of its own. That way you can also allow chats between more than just two users if you wish.
(If user_name was meant to be in the query, you forgot to alias users.name AS user_name.)
I’d suggest something like this (leaving out timestamp columns and such things):
# Table: users
- id
- name, etc.
# Table: chats
- id
- (whatever other properties a chat may have)
# Table: messages
- id
- user_id
- chat_id
- content (or message or whatever you want to call it)
# Pivot table: chats_users
- id
- chat_id
- user_id
Your present query would be made somewhat simpler as well by that, since you could just fetch all chats on the current user ($user->belongsToMany(Chat::class)) and get the latest message in each chat – that would then not care whether the message is to or from the current user, which is as you’d probably want it for a chat application.
Level 3
I got the expected output with the below code. I know there is some improvement in my code, but it is functional for now.
$user_id = $request->user_id;
$user_name = DB::table('users')->where('id', $user_id)->value('name');
$chats =DB::table('messages')
->leftJoin('users as u1', 'messages.from_id', '=', 'u1.id')
->leftJoin('users as u2', 'messages.to_id', '=', 'u2.id')
->select('u1.id as user_id', 'u1.name', 'u2.name as name2', 'messages.id','messages.from_id',
'messages.to_id', 'messages.body as message', 'messages.attachment', 'messages.created_at')
->where('messages.from_id', $user_id)
->orWhere('messages.to_id', $user_id)
->latest()
->get();
foreach($chats as $chat)
{
$chat->user_id = $chat->from_id == $user_id ? $chat->to_id : $chat->from_id;
$chat->name = $chat->name == $user_name ? $chat->name2 : $chat->name;
}
$chats = $chats->unique('user_id');
1 like
Level 56
@Zafeer now it is clearer what you wanted.
If you accept a suggestion to avoid the loop and the unique just after the query:
$user_id = $request->user_id;
$chats = DB::table('messages')
->leftJoin('users as u1', 'messages.from_id', '=', 'u1.id')
->leftJoin('users as u2', 'messages.to_id', '=', 'u2.id')
->selectRaw('CASE WHEN u1.id = ? THEN u2.id ELSE u1.id END AS user_id', [$user_id])
->selectRaw('CASE WHEN u1.id = ? THEN u2.name ELSE u1.name END AS name', [$user_id])
->addSelect([
'messages.id',
'messages.from_id',
'messages.to_id',
'messages.body as message',
'messages.attachment',
'messages.created_at',
])
// When using OR with WHERE clauses, it is a nice idea to wrap
// them into a nested ->where(), as this the equivalent
// to wrap then into parenthesis on a SQL clause
// this way, if you need to add more WHERE clauses later,
// they won't be affected by these.
->where(function ($query) use ($user_id) {
$query->orWhere('messages.from_id', $user_id)
->orWhere('messages.to_id', $user_id);
})
->latest()
->limit(1) // avoid the unique call
->get();
You could replace the ->limit(1)->get() by a ->first() call.
I kept the ->limit(1)->get() as you might always be expecting a collection result.
1 like
Level 3
@rodrigo.pedra thanks, it looks good i would definitely implement this into my code.
Please or to participate in this conversation.