CODEWITHSUNDEEP
mysql
TRomesh
TRomesh

Reputation: 4481

How to get the last row from each users?

im making a messaging system just like facebook messages section. where i want to display a list of conversations between the user and his friends. the fields of my chat table are

id,user1(varchar),user2(varchar),message,created_at(timestamp),updated_at(timestamp)

to retrieve a certain conversation i'm using a query 

SELECT message FROM chat WHERE user1 IN ('"+user1+"','"+user2+"') AND user2 IN ('"+user1+"','"+user2+"')

but now i'm having trouble to retrieve latest(recently inserted) row of the message,the other user(the one whose receiving our message) and the time sent. which is to be shown by a list(just like facebook conversation list). it means i want a list of the most recent message (one) from each of the users on the chat table. or if im chatting with you i want to receive your name along with the message.

I came up with a query and I want you to see it its not the the final answer that i'm looking for but its pretty close to it. 

SELECT sent_at,message,user1,user2 FROM (SELECT max(created_at) AS 
sent_at,message,user1,user2 FROM chats WHERE user1='Tiffany' OR 
user2='Tiffany' Group By user1, user2 Having max(created_at)) tb1

It returns the last conversation that the user 'Tiffany' has done, it means the latest messages send and received by tiffany. now i want to get the latest conversation either received or send by tiffany.

Upvotes: 0

Views: 132

Answers (4)

TRomesh
TRomesh

Reputation: 4481

I got what i wanted i had to use MAX() to get the correct conversation.

SELECT sent_at,
       message,
       user1,
       user2
FROM 
   (SELECT max(created_at) AS sent_at,
          message,
          user1,
          user2 
   FROM `chats` 
   WHERE user1='Tiffany' 
   OR user2='Tiffany' 
   Group By user1,
         user2
   Having max(created_at))tb1

Upvotes: 1

Martin
Martin

Reputation: 22760

If I understand your question correctly then what you are looking for is a listing for the most recent message (one) from each of the contacts on the members contact list?

So, you need to grab one message from the database for each correspondant, and the one you grab needs to be the most recent.

These two criteria now mean that you/we can apply logic to the SQL to achieve this result.

So;

Usig this SQL table column format you have provided:

id,user1(varchar),user2(varchar),message,updated_at(timestamp),updated_at(timestamp)

And using the assumption that the member whose messages you want to list is in user1 column, we can construct:

SELECT message, FROM_UNIXTIME(MAX(updated_at)) AS chat_time 
FROM chat WHERE user1 = :username ORDER BY updated_at GROUP BY user2

So foreach value of user2 the SQL lists the maximum timestamp value, which is the most recent message from that user.

What I wanted to do with this was that if the user is featured in either user1 or user2 to GROUP BY the opposing column, so if the user is in user1 then group by user2 and vice versa, however I can't easily get to do this right now and would probably involve HAVING which I don't have as much experience with as I'd like.

Possibly a UNION would do this but I would like to find a more neat solution here:

    SELECT message, FROM_UNIXTIME(MAX(updated_at)) AS chat_time 
    FROM chat WHERE user1 = :username ORDER BY updated_at GROUP BY user2 
UNION
    SELECT message, FROM_UNIXTIME(MAX(updated_at)) AS chat_time 
    FROM chat WHERE user2 = :username ORDER BY updated_at GROUP BY user1

Upvotes: 1

Jeeva Balan
Jeeva Balan

Reputation: 393

I think this will work

SELECT message FROM chat WHERE user1 IN ('"+user1+"','"+user2+"') AND user2 IN ('"+user1+"','"+user2+"')ORDER BY created_at DESC LIMIT 1

Upvotes: 1

Priyanshu
Priyanshu

Reputation: 881

SELECT message FROM chat WHERE user1 IN ('"+user1+"','"+user2+"') AND user2 IN ('"+user1+"','"+user2+"') order by message desc limit 1;

Upvotes: 1

Related Questions