Eloquent complex relationship for a chat message system in laravel

I am writing a chat system and I have the following tables:

chat_conversations: id, name

chat_messages: id, chat_conversation_id, message, deleted_at

chat_message_user: id, chat_message_id, user_id, is_sender, seen, deleted_at

chat_conversation_user: id, user_id, chat_conversation_id, is_owner

I want the messages to be linked to the users seperately hence the chat_message_user such that each message can be deleted individually but remain visible for the other user(s) in the chat. For example, if I am chatting with someone else, and I want to ‘delete’ the conversation on my end only, I want the other person to still have that chat history on their end.

I want to be able to query the conversations that belong to the auth::user and append the messages that only THEY can see (the ones where the chat_message_user is NOT soft deleted)

Auth::user()->conversations 

User Model:

...

public function conversations()
{
   return $this->belongsToMany(ChatConversation::class)
               ->withTimestamps();
}

...

ChatConversation Model:

protected $appends = ['messages'];

public function messages()
{
   return $this
       ->hasMany(ChatMessage::class)
       ->whereHas('user') //I want whereHas('user') to reference only the user id from the Auth::user()
       ->get();
}

public function getMessagesAttribute()
{
   return $this->messages();
}
...

Currently, I am trying to use a whereHas(‘user’) on my messages method but in order for it to work as I intended, I need to pass in a user id… Ideally I just want to be able to reference the Auth::user->id from the start of the query… But if I have a parameter on the messages method, it will not append…

->whereHas('user', function ($query) use ($user_id) {
    $query->where('user_id', $user_id);
})

In a nut shell, I want the conversation to return with it all of the messages that belong to the current user which are not deleted for that particular user via the chat_message_user pivot table.

Answer

I would create a relationship on the conversation to only query the messages for the authenticated user.

In ChatConversation

public function userMessages()
{
   return $this
       ->hasMany(ChatMessage::class)
       ->whereHas('user', function ($query) {
           $query->where('user_id', Auth::id());
        });
}

Note that without calling ->get(), this is an actuall laravel relationship and not a collection. Now, you can query your messages easily once you have the $conversation object:

$conversation->userMessages; // Collection of all the messages for the user in the conversation

$conversation->userMessages()->limit(10)->get(); // Only query 10 messages

---

You can also load the messages with the conversations using eager loading:

User::find(1)->conversations()->with(['userMessages' => function ($query) {
  $query->orderBy('created_at', 'DESC')->limit(10);
}]);