Get last message in a conversation in sql

Question

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

usermeta

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

Message

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

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.

Answer 1

trye this :

select t.sender,t.reciepent,m2.content 
from 
(
select
sender,reciepent,
(select count(*) from messages m where m.sender = sender and m.reciepent = reciepent) sent,
(select count(*) from messages m where m.sender = reciepent and m.reciepent = sender) rec
from messages m
) t 
inner join messages m2 on t.sender = m2.sender and t.reciepent = m2.reciepent
inner join usermeta u on u.userid = t.sender
where t.sent>t.rec and m2.date = (select max(m3.date) from messages m3 where m3.sender = m2.sender and m3.reciepent = m2.reciepent) and u.metakey='thekey'

Answer 2

If you have any unique/ primary id that stores your chat than to retrieve last message you can use mysql function called : mysql_insert_id(); It will give you last inserted message's id and by id you can retrieve your message.

Thanks.

Answer 3

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