Is there a simpler way to achieve this style of user messaging?

Question

I have created a messaging system for users, it allows them to send a message to another user. If it is the first time they have spoken then a new conversation is initiated, if not the old conversation continues.

The users inbox lists all conversations the user has had with all other users, these are then ordered by the conversation which has the latest post in it.

A user can only have one conversation with another user.

When a user clicks one of these conversations they are taken to a page showing the whole conversation they've had with newest posts at the top. So it's kind of like a messaging chat functionality.

I have two tables:

• userconversation
• usermessages

userconversation

Contains an auto increment id which is the conversation id, along with the userId and the friendId.

Whoever initates the first conversation will always be userId and the recipient friendId, this will then never change for that conversation.

+----+--------+----------+ | id | userId | friendId | +----+--------+----------+ 

usermessages

Contains the specific messages, along with a read flag, the time and conversationId

+----+---------+--------+------+------+----------------+ | id | message | userId | read | time | conversationId | +----+---------+--------+------+------+----------------+ 

How it works

When a user goes to message another user, a query will run to check if both users have a match in the userconversation table, if so that conversationId is used and the conversation carries on, if not a new row is created for them with a unique conversationId.

Where it gets complicated

So far all is well, however when it comes to displaying the message inbox of all conversations, sorted on the latest post, it get's tricky to do with one query..

To be able to list the conversations you must first find the latest post of each conversation, but as you can't order by before a group this is impossible to do with one query on two tables, so I have to use the following:

SELECT       c.id,     c.userId,     c.friendId,     m2.message,     m2.read,     UNIX_TIMESTAMP(m2.time),           user1.username,     user2.username   FROM      (SELECT MAX(m1.id) AS MessageID       FROM usermessages m1       GROUP BY m1.conversationId) latest_msg  INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id  INNER JOIN userconversation c ON m2.conversationId = c.id INNER JOIN user user1 ON c.userId = user.id INNER JOIN user user2 ON c.friendId = user.id  WHERE c.userId = :userId OR c.friendId = :userId ORDER BY m2.id DESC LIMIT 10 

I just don't think this is the best way it can be done but can't think of others ways too approach it?

The database table is InnoDB to speed up the joins and improve data integrity so I can't have two auto increment rows.

Is there another way I could get rid of the userconversation table and create a unique Id to put in the conversationId column? I could then just move the userId and friendId over to usermessages ... but this would create a lot of redundant data?

Answer 1

hmm maybe i'm not understanding correctly your problem... but to me the solution is quite simple:

SELECT c.*, MAX(m.time) as latest_post  FROM conversations as c  INNER JOIN messages as m ON c.id = m.conversation_id WHERE c.userId = 222 OR c.friendId = 222  GROUP BY c.id ORDER BY latest_post DESC 

here's my test data:

Conversations :

id  userId  friendId 1   222     333 2   222     444 

Messages :

id  message     time (Desc)     conversation_id 14  rty     2012-05-14 19:59:55     2 13  cvb     2012-05-14 19:59:51     1 12  dfg     2012-05-14 19:59:46     2 11  ert     2012-05-14 19:59:42     1 1   foo     2012-05-14 19:22:57     2 2   bar     2012-05-14 19:22:57     2 3   foo     2012-05-14 19:14:13     1 8   wer     2012-05-13 19:59:37     2 9   sdf     2012-05-13 19:59:24     1 10  xcv     2012-05-11 19:59:32     2 4   bar     2012-05-10 19:58:06     1 6   zxc     2012-05-08 19:59:17     2 5   asd     2012-05-08 19:58:56     1 7   qwe     2012-05-04 19:59:20     1 

Query result :

id  userId  friendId    latest_post 2   222     444     2012-05-14 19:59:55 1   222     333     2012-05-14 19:59:51 

If that's not it... just ignore my answer :P

Hope this helps