MySQL: Creating a list of conversations (last msgs to/from user_id)

Question

Hi,

I have a mysql table containing messages:

id (INT)
sender_id (INT)
recipient_id (INT)
sent_time (INT)
read_time (INT)
body (TEXT)

I need to retrieve a list of most recent messages user X received from or sent to other (distinct) users.

In other words I'd like to create a full list of conversations with other users, sorted by sent_time of last message exchanged with a given user. The end result (processed in PHP) is going to like like this:

array (
    array (other_user_1_id, last_msg_sent_time, last_msg_is_read,
           last_msg_is_to_me/from_me, number_of_messages*),
    array (other_user_2_id, ... )
)

*) number_of_messages is optional, but rather nice to have.

Question: How do I do this most efficiently? Create a separate table "conversations" and update it every time anyone sends or reads a message or create a single, but sophisticated query on "messages" table? If the latter, what would the query look like?

Answer 1

Something along these lines:

$id = 12345 // Id for the user you are pulling messages for
$messages = Array();

$result = mysql_query("SELECT sender_id, recepient_id, sent_time, read_time FROM messages WHERE sender_id = $id OR recepient_id = $id");

while ($row = mysql_fect_assoc($result)) {

  $directon = ( $row['recepient_id'] == $id ) ? 'to' : 'from';
  $isRead = ( (bool)$row['read_time'] );
  ....
  ....
  $messages[] = Array( $direction, $isRead .......... )

}

for number of messages in a convo, you will have to make a new table for conversations, or work out a logic governing what makes a set of messages fall into a specific conversation.

Answer 2

I would take the advice of the code given in codeburger's post but to add to that you have other options (as it sounds like his code is not anything new in regards to a solution).

Possible options include:

1. indexing on both the sender_id (INT) and recipient_id (INT) to increase search time

2. creating a separate table for each sender_id (that way when you know who the conversation is between - assuming it is only a 1 on 1 conversation - you can just pull from two tables where the ids are common)

3, my favorite idea, create an additional column that references a transaction database and keep a history of each transaction, indexed off the conversation number... it's probably the "worst" in some ways, but it's more my organizational style.

if you have any questions, i can elaborate, in fact, i'll probably update this later once i get home