I'm developing a message chat system, but I have a problem with making a list of chat rooms, currently I have three tables

users
id | name      | avastar     
1  | name1     | /img/avatar/1.jpg          
2  | name2     | /img/avatar/2.jpg        
3  | name3     | /img/avatar/3.jpg           
         
user_messages
id | message_id | sender_id   | receiver_id | created_at
1  | 1          | 1           | 3           |  2022-12-13 19:34:11
2  | 2          | 3           | 1           |  2022-12-13 19:35:00
3  | 3          | 1           | 2           |  2022-12-14 16:28:00
4  | 4          | 1           | 2           |  2022-12-15 09:39:00
5  | 5          | 2           | 3           |  2022-12-15 10:01:00

messages
id | message 
1  | hello        
2  | hello    
3  | hello       
4  | how are you         
5  | hi      

user1 chat list displays a message with user2 and user3 This message requires user2 and user3 's name and avatar last message content and send time

How to query?

class UserMessage extends Model
{
    public function message(){
        return $this->belongsTo(Message::class);
    }
}

class Message extends Model
{
    public function user_messages(){
        return $this->hasMany(UserMessage::class);
    }

    public  function  users(){
        return $this->belongsToMany(User::class, 'user_messages', 'message_id', 'sender_id')->withTimestamps();
    }
}

I want the list of this page to display the content of the image below

enter image description here

zohrehda's answer is awesome share the following code it works.

 $messages=UserMessage::
            where('receiver_id',$user_id)
            ->orWhere('sender_id',$user_id)
            ->LeftJoin('messages','user_messages.message_id','messages.id')
            ->select('user_messages.*','messages.message')
            ->get();
        $chat_list=[] ;
        foreach($messages as $message){
            if($message->receiver_id !=$user_id)
            {
                $chat_list[$message->receiver_id][]=$message ;

            }elseif($message->sender_id !=$user_id){

                $chat_list[$message->sender_id][]=$message ;

            }

        }
        $array=[] ;
        foreach($chat_list as $user_id=>$messages)
        {
            $user=User::find($user_id) ;
            $array[]=[
                'user_name'=> $user->name ,
                'user_avatar'=>$user->avatar ,
                'last_message' =>last($messages)->message,
                'created_at'=>last($messages)->created_at
            ];
        }

CodePudding user response：

first you need to gather messages related to the user that means messages which receiver or sender of that is the current user.

then loop in this messages and create a chat_list array that the key is the user id and the value is all messages that the current user and specified user had conversation.

$messages=Message::where('receiver_id',$user->id)->orWhere('sender_id',$user->id)->get();
$chat_list=[] ;
foreach($messages as $message){
   if($message->receiver_id !=$user->id)
   { 
    $chat_list[$message->receiver_id][]=$message ;
    
   }elseif($message->sender_id !=$user->id){
   
    $chat_list[$message->sender_id][]=$message ;

  }
 
}
$array=[] ;
foreach($chat_list as $user_id=>$messages)
{
   $user=User::find($user_id) ;
   $array[]=[
  
       'user_name'=> $user->name ,
       'user_avatar'=>$user->avatar ,
       'last_message' =>last($messages)->message->message ,
       'created_at'=> last($messages)->created_at
 
   ];
}

remmember $chat_list is a the list of user chat. the key is user contact and the value is all their conversation. $array variable is exact what use need

I hope this work.