使用拉拉维尔关系获取对话线程，包括最后一条消息

Question

我无法使用模型关系获取每个对话的最后一条消息和未读消息的数量：

|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   |            |            |   

我已经构建了检索特定用户对话线程的查询，包括与收件人的关系：

模型：应用\模型\消息.php

public function userTo()
{
return $this->belongsTo('AppModelsUser', 'recipient_to', 'id');
}

控制器

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

响应：

[
{
"recipient_to": 2,
"user_to": {
"id": 2,
"name": "Jessyca",
"last_name": "Skiles",
"avatar": "https://i.pravatar.cc/150?u=[e-mail]"
}
},
{
"recipient_to": 3,
"user_to": {
"id": 3,
"name": "Rocio",
"last_name": "Rosenbaum",
"avatar": "https://i.pravatar.cc/150?u=[e-mail]"
}
}
]

如何建立返回最后一条消息和未读消息数的关系？

我已经读过这篇文章，我认为它很有趣，虽然目的是研究拉拉维尔的关系 在Laravel中获取每个用户的最新消息(行(

21/

08/2019 ---进展

如果我在 User 模型中创建两个关系，我会收到对话中每个用户发送的最后一条消息，有没有办法创建一个额外的关系并合并以返回最新的关系？

public function latestMessageTo()
{
return $this->hasOne('AppModelsMessage', 'recipient_to')->orderBy('created_at', 'desc')->latest();
}
public function latestMessageFrom()
{
return $this->hasOne('AppModelsMessage', 'user_id')->orderBy('created_at', 'desc')->latest();
}

Answer 1

我找到了一个解决方案，不要使用雄辩，但它满足了需求。

也许我们可以使用 eloquent 找到一个解决方案，尽管我已经读到使用本机 SQL 查询而不是 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]);

有什么意见吗？

我想归功于我找到查询的出版物：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