Get thread of conversations including the last message using laravel relationship

Question

I'm having trouble trying to get the last message for each conversation and the number of unread messages using the model relationship:

|id | user_id | recipient_to |    message        | read | created_at | updated_at | 
+---+---------+--------------+-------------------+------+------------+------------+
| 1 |    1    |      2       | Hi                |  1   |            |            |
| 2 |    2    |      1       | How are you?      |  1   |            |            |   
| 3 |    1    |      3       | Hi                |  0   |            |            |
| 4 |    1    |      2       | Could you help me?|  0   |            |            |

I have built the query that retrieves the thread of conversations of a specific user including the relation to the recipient:

Model: App\Models\Message.php

public function userTo()
{
   return $this->belongsTo('App\Models\User', 'recipient_to', 'id');
}

Controller

$conversations = Message::where('user_id', 1)
     ->with([
        'userTo' => function ($query) {
            $query->select('id', 'name', 'last_name', 'avatar');
         }
     ])
     ->distinct()
     ->get(['recipient_to']);

Response:

[
    {
        "recipient_to": 2,
        "user_to": {
            "id": 2,
            "name": "Jessyca",
            "last_name": "Skiles",
            "avatar": "https://i.pravatar.cc/150?u=hayes.rachael@example.org"
        }
    },
    {
        "recipient_to": 3,
        "user_to": {
            "id": 3,
            "name": "Rocio",
            "last_name": "Rosenbaum",
            "avatar": "https://i.pravatar.cc/150?u=yspencer@example.org"
        }
    }
]

How could I build the relationship that returns the last message and the number of unread messages?

I have already read this post, I consider it very interesting, although the purpose is to study the relations of Laravel Get latest message (row) per user in Laravel

21/08/2019 --- Progress

If I create two relationships in the User model, I get the last message sent from each user in the conversation, is there any way to create an additional relationship and merge to return the most recent?

public function latestMessageTo()
{
   return $this->hasOne('App\Models\Message', 'recipient_to')->orderBy('created_at', 'desc')->latest();
}

public function latestMessageFrom()
{
   return $this->hasOne('App\Models\Message', 'user_id')->orderBy('created_at', 'desc')->latest();
}

score 2 · Accepted Answer · answered Sep 06 '19 at 16:25

I found a solution, don't use eloquent, however it satisfies the need.

Perhaps we could find a solution using eloquent, although I have read that it's more efficient to use a native SQL query instead of eloquent.

         DB::select('
            SELECT t1.*
            FROM messages AS t1
            INNER JOIN
            (
                SELECT
                    LEAST(user_id, recipient_to) AS user_id,
                    GREATEST(user_id, recipient_to) AS recipient_to,
                    MAX(id) AS max_id
                FROM messages
                GROUP BY
                    LEAST(user_id, recipient_to),
                    GREATEST(user_id, recipient_to)
            ) AS t2
                ON LEAST(t1.user_id, t1.recipient_to) = t2.user_id AND
                   GREATEST(t1.user_id, t1.recipient_to) = t2.recipient_to AND
                   t1.id = t2.max_id
                WHERE t1.user_id = ? OR t1.recipient_to = ?
            ',[auth()->guard('api')->user()->id, auth()->guard('api')->user()->id]);

any opinion?

I want to give credit to the publication where I found the query: https://laracasts.com/discuss/channels/laravel/get-the-latest-message-of-chat-model-with-mysql-just-cannot-get-the-idea-how-to-do-this?page=1#reply=392529

Get thread of conversations including the last message using laravel relationship

1 Answers1