从每个会话中获取最后一条消息
从每个会话中获取最后一条消息
提问于 2015-12-18 03:35:56
我知道以前也有人问过类似的问题，但他们都没有同样的条件，他们的答案对这种情况不起作用。
包含消息的表如下所示：
id | owner_id | recipient_id | content | created
1 | 1 | 2 | Hello | 2015-12-08 20:00
2 | 2 | 1 | Hey | 2015-12-08 20:10
3 | 3 | 1 | You there? | 2015-12-08 21:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00假设我从用户ID为1的每个会话中查询最后一条消息，预期结果为：
id | owner_id | recipient_id | content | created
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
2 | 2 | 1 | Hey | 2015-12-08 20:10我尝试了许多组合，使用连接和子查询，但都没有给出预期的结果。
这是我尝试过的一个查询，但它不起作用。我相信这和我需要的还差得很远。
SELECT
IF ( owner_id = 1, recipient_id, owner_id ) AS Recipient,
(
SELECT
content
FROM
messages
WHERE
( owner_id = 1 AND recipient_id = Recipient )
OR
( owner_id = Recipient AND recipient_id = 1 )
ORDER BY
created DESC
LIMIT 1
)
FROM
messages
WHERE
owner_id = 1
OR
recipient_id = 1
GROUP BY
Recipient;回答 3
Stack Overflow用户
回答已采纳
发布于 2015-12-18 04:12:08
select t.*
from
t
join
(select user, max(created) m
from
(
(select id, recipient_id user, created
from t
where owner_id=1 )
union
(select id, owner_id user, created
from t
where recipient_id=1)
) t1
group by user) t2
on ((owner_id=1 and recipient_id=user) or
(owner_id=user and recipient_id=1)) and
(created = m)
order by created descStack Overflow用户
发布于 2015-12-18 05:12:44
这应该能起到作用：
$joins = array(
array('table' => 'conversations',
'alias' => 'Conversation2',
'type' => 'LEFT',
'conditions' => array(
'Conversation.id < Conversation2.id',
'Conversation.owner_id = Conversation2.owner_id',
)
),
array('table' => 'conversations',
'alias' => 'Conversation3',
'type' => 'LEFT',
'conditions' => array(
'Conversation.id < Conversation3.id',
'Conversation.recepient_id = Conversation3.recepient_id',
)
)
);
$conditions = array(
'OR' => array(
array(
'Conversation2.id'=>null,
'Conversation.owner_id' => $ownerId
),
array(
'Conversation3.id'=>null,
'Conversation.recipient_id' => $ownerId
),
)
);
$order = array('Conversation.created'=>'DESC');
$lastConversations=$this->Conversation->find('all',compact('conditions','joins','order'));假设表的名称是conversations，模型的名称是Conversation。它基于Retrieving the last record in each group公认答案中描述的技术。
Stack Overflow用户
发布于 2020-06-28 16:22:16
这个解决方案最适合我。
SELECT t1.*
FROM chats AS t1
INNER JOIN
(
SELECT
LEAST(sender_id, receiver_id) AS sender_id,
GREATEST(sender_id, receiver_id) AS receiver_id,
MAX(id) AS max_id
FROM chats
GROUP BY
LEAST(sender_id, receiver_id),
GREATEST(sender_id, receiver_id)
) AS t2
ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
t1.id = t2.max_id
WHERE t1.sender_id = ? OR t1.receiver_id = ?页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34342835
复制相关文章
相似问题