在sql中获取对话中的最后一条消息 [英] Get last message in a conversation in sql

问题描述

我需要获取未答复的最新消息考虑以下表格:

I need to get the newest message that is unanswered consider the following tables:

用户元数据

userid   metakey    metavalue
-----|------------|-------
 12  | "thekey"   | true
 41  | "thekey"   | true

留言

sender  reciepent   content       date
-----|------------|--------------|-----------------
 12  | 0          | "lorem ipsum"|2013-08-12 21:20:31
 0   |12          | "lorem ipsum"|2013-08-12 20:20:31
 41  |50          | "lorem ipsum"|2013-08-12 18:20:31
 50  |41          | "lorem ipsum"|2013-08-12 19:20:31

现在我想要获得消息，这些消息没有被对话的其他人回答，并且是由拥有 usermeta "thekey 的用户编写的" = 真

Now i want to get messages that were not answered by the other person of the conversation and that are written by a user that has the usermeta "thekey" = true

我尝试了几种想法，但都没有奏效.我很想得到一些帮助.对不起，如果我做错了什么，这是我第一次在这里发帖.

i tried several thinks but none worked. I would love to get some help. Sorry if i did some things wrong, this is my first Post here.

推荐答案

因为您使用的是 Wordpress，所以我假设您也在使用 MySQL.我手头没有任何 MySQL 数据库，但我使用 T-SQL 将这个查询放在一起，您可以将其转换为 MySQL.

Since you are using Wordpress I assume you're also using MySQL. I don't have any MySQL database at hand but I put this query together using T-SQL which you can translate to MySQL.

这个想法是使用一个临时表来存储每个会话中每个用户的最后一条消息，然后找出哪些未被回答并检查用户元数据.我希望这对您有所帮助.

The idea is to use a temporary table to store the last message from each user in each conversation, then find which ones were unanswered and also check for the user metadata. I hope this helps you.

create table #tmpLastMsg (
    sender int,
    reciepent int,
    msgdate datetime
)

-- get the last message from each user in every conversation
insert into #tmpLastMsg
select sender, reciepent, MAX(msgdate)
from Message
group by sender, reciepent

select m.*
from Message as m
inner join (
    select msg.*
    from #tmpLastMsg as msg
    left join #tmpLastMsg as rep -- this join tries to find if there was a reply
        on msg.sender = rep.reciepent
        and msg.reciepent = rep.sender
        and rep.msgdate > msg.msgdate
    inner join usermeta as um on msg.sender = um.userid
    where rep.sender is null -- we want unreplied messages
        and um.metakey = 'thekey'
        and um.metavalue = 'true'
) as t on m.sender = t.sender
    and m.reciepent = t.reciepent
    and m.msgdate = t.msgdate

这篇关于在sql中获取对话中的最后一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！