Returning multiple recipients names in MySQL (messaging system)

I'm creating a mult开发者_开发问答iple-recipient threaded messaging system similar to the iPhone SMS system (where both inbox and outbox are combined into one display, and only one thread can exist between two users).

Messages are stored using a senderid column (e.g. "11") and a recipients column (e.g. ",13,10,15,20,"). In order to store multiple recipients, I seperate them out with commas and query the field with a "%" + ",$userid," + "#" call. The problem with this is that it messes up the LEFT JOIN which connects the recipientsid to the users table (which has the name of the users).

Here's my current query:

$sql = 
  "SELECT DISTINCT CASE 
   WHEN $userid != senderid THEN senderid ELSE recipients END someid,   
   CASE WHEN 13 != senderid THEN senders.username ELSE receivers.username END somename,
   messages.body, 
   messages.time 
   FROM messages 
   LEFT JOIN users AS senders ON messages.senderid = senders.id
   LEFT JOIN users AS receivers ON messages.recipients = receivers.id
   WHERE messages.recipients = '#' + ',$userid,' + '#'
   OR messages.senderid = $userid
   ORDER BY messages.time";

Here's what I'd like to do:

• If there is only one recipient id in recipients (the content looks like ",id,", then remove the commas surrounding the id to find the sendername (LEFT JOIN users AS receivers ON messages.recipients(WITHOUT COMMAS) = receivers.id.

• If there is more than one recipient id in recipients (the content looks like ",id,id2,id3," etc.), somehow pull all the names which correspond to the IDs and store the result similar to the following: sendername = "Name1, Name2, Name3";.

Anyone have any idea how I would approach this? A subquery maybe? I'd really appreciate the help.

---

1st. Instead of:

WHERE messages.recipients = '#' + ',$userid,' + '#'

you can drop the starting and ending commas and use :

WHERE FIND_IN_SET($userid, messages.recipients) > 0

So you could also have this condition for the LEFT JOIN:

LEFT JOIN users AS receivers
  ON FIND_IN_SET(receivers.id, messages.recipients) > 0

and then group by message and concat the names with GROUP_CONCAT().

Something like this:

  SELECT senders.name AS senderName
       , messages.body
       , messages.time 
       , GROUP_CONCAT( receivers.name
                       ORDER BY receivers.id
                       SEPARATOR ','
                     )
         AS receiversNames
  FROM messages 
    LEFT JOIN users AS senders
      ON messages.senderid = senders.id
    LEFT JOIN users AS receivers
      ON FIND_IN_SET(receivers.id, messages.recipients) > 0
  GROUP BY messages.id
  ORDER BY messages.time

will give you all messages with sender's name and receivers' names concatenated.

I guess that every message has exactly 1 (or zero) senders. If not, and some messages have more than 1 senders, then you'll probably need to edit the GROUP BY.

Warning: This type of queries may be slow (or even terribly slow) when there will be many rows in the tables. No index can be used on messages.recipients and thus slow down the query. So, you better consider now the option of normalizing your structure with a MessageRecipient intermediate table.