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

Asked 2012-05-14T17:43:05.270

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 t

Edit Report

3 Answers

How to create a fast Facebook-like messages system. tested and widely used by Arutz Sheva users - http://www.inn.co.il (Hebrew).

create a "topic" (conversation) table:

  CREATE TABLE pb_topics (
  t_id int(11) NOT NULL AUTO_INCREMENT,
  t_last int(11) NOT NULL DEFAULT '0',
  t_user int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (t_id),
  KEY last (t_last)
) ENGINE=InnoDB AUTO_INCREMENT=137106342 DEFAULT CHARSET=utf8

create link between user and conversation:

    CREATE TABLE pb_links (
  l_id int(11) NOT NULL AUTO_INCREMENT,
  l_user int(11) NOT NULL DEFAULT '0',
  l_new int(11) NOT NULL DEFAULT '0',
  l_topic int(11) NOT NULL DEFAULT '0',
  l_visible int(11) NOT NULL DEFAULT '1',
  l_bcc int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (l_id) USING BTREE,
  UNIQUE KEY topic-user (l_topic,l_user),
  KEY user-topicnew (l_user,l_new,l_topic) USING BTREE,
  KEY user-topic (l_user,l_visible,l_topic) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=64750078 DEFAULT CHARSET=utf8

create a message

    CREATE TABLE pb_messages (
  m_id int(11) NOT NULL AUTO_INCREMENT,
  m_from int(11) NOT NULL,
  m_date datetime NOT NULL DEFAULT '1987-11-13 00:00:00',
  m_title varchar(75) NOT NULL,
  m_content mediumtext NOT NULL,
  m_topic int(11) NOT NULL,
  PRIMARY KEY (m_id),
  KEY date_topic (m_date,m_topic),
  KEY topic_date_from (m_topic,m_date,m_from

answered 2012-05-14T18:05:44.200

it is being used on fiverr.com and www.infinitbin.com. I developed the infinitbin own. It has two databases like yours too. the inbox table:-

+----+--------+----------+-------------+------------+--------------------------------+
| id | useridto | useridfrom | conversation | last_content | lastviewed | datecreated|
+----+--------+----------+-------------+------------+--------------------------------+

This table is very important, used to list the conversations/inbox. The last_content field is 140 charaters from the last message between the conversations. lastviewed is an integer field, the user who lasts sends a message is the last viewed, if the other user in the conversation reads the message. it gets updated to NULL. Therefore to get notifications, you for lastviewed that is not null and not the logged in user's id.

The conversation field is 'userid-userid', therefor strings. To check if users have started a conversation, you concatenate there user_ids with a hyphen and check it.

This kind of messaging system is a very complicating one.

The second table is quite simple.

+----+--------+----------+-------------+-------+
| id | inboxid | userid | content | datecreated|
+----+--------+----------+-------------+-------+

answered 2012-05-20T10:14:38.230

I think you do not need to create a userconversation table.

If only user can have only one conversation with someone, the unique id for this thread is a concat between userId and friendId. So I move the friendId column in usersmessage table. The problem of order (friendId-userId is the same thread of userId-friendId) can be solved so:

SELECT CONCAT(GREATEST(userId,FriendId),"_",LEAST(userId,FriendId)) AS threadId

Now there is a problem of fetch the last message after a GROUP BY threadId.

I think is a good solution make a concat between DATE and message and after a MAX on this field.

I assume, for simplicity, column date is a DATETIME field ('YYYY-mm-dd H:i:s') but it not need because there is FROM_UNIXTIME function.

So the final query is

SELECT 
        CONCAT(GREATEST(userId,FriendId),"_",LEAST(userId,FriendId)) AS threadId,
        friendId, MAX(date) AS last_date, 
        MAX(CONCAT(date,"|",message)) AS last_date_and_message 

FROM usermessages
WHERE userId = :userId OR friendId = :userId
GROUP BY threadId ORDER BY last_date DESC

the result of field last_date_and_message is something like so:

2012-05-18 00:18:54|Hi my friend this is my last message

it can be simply parsed from your server side code.

answered 2012-05-24T17:34:06.020

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

3 Answers

Your Answer