Help with conversation system

MySQL database
#1

Hello, I’m making a conversation system for a website.
I have 3 table:

  • conversations (cid, subject, dateline)
  • conversations_users (cid, uid, lastread)
  • conversations_messages (mid, cid, uid, message, dateline, …)

Now I need to get the conversation of the user with all the participants IDs and order by last message, can you help me build the query? At the moment I have this query:

            SELECT c.cid, c.subject, GROUP_CONCAT(cp.uid) AS participants FROM conversations c
            JOIN conversations_users cu ON (cu.cuid = c.cid AND cu.uid = 3)
            JOIN conversations_users cp ON cp.cid = c.cid
            LEFT JOIN conversations_messages cm ON cm.cid = c.cid
            ORDER BY cm.dateline DESC
            LIMIT 0, 20
#2

I also need to get the user id and time of the last message

#3

What isn’t showing? Aside from you not including the actual messages in the query.

#4

I don’t know how to get only the last message, with this query it will get all the messages of the conversations.

#5

If you only want the last, then you need to change the limit clause to select 1.

#6

I need to get all the conversations but only the last message of them. If I use limit 1 it will only get 1 conversation.

#7

This isn’t making any sense…

#8

What do you mean?
I need to show on the page the conversation title and at the right what is the last message of that conversation. Why doesn’t it make any sense?

#9

Maybe I should add a new column to the conversations table called last_message_id but I don’t know if that’s a good method.

#10

Are you not using timestamps?

$sql = 'SELECT c.cid, c.subject, GROUP_CONCAT(cp.uid) AS participants, cp.uid, cm.message FROM conversations c
            JOIN conversations_users cu ON (cu.cuid = c.cid AND cu.uid = 3)
            JOIN conversations_users cp ON cp.cid = c.cid
            LEFT JOIN conversations_messages cm ON cm.cid = c.cid
            ORDER BY cm.dateline DESC
            LIMIT 0, 20';

$stmt->prepare($sql);
$stmt->execute();
$resp = $stmt->fetchAll();

$title = null;
$members = null;
echo "<div>{$resp[0]['participants']}{{$resp[0]['subject']}</div>";
foreach($resp as $msg) {
	echo "<div><p>{$msg['cp.uid']}</p><p>{$msg['message']}</p></div>";
}
#11

I think you misunderstand me. I need to show something like that:

Subject Participants Last Message
Conversation 1 Mark, Andrew, Alex Yesterday by Alex
Conversation 2 Mark, Jon 3 days ago by Mark
#12

Yes, I did misunderstand in that case.

So, do you have a timestamp or datetime column for when the message was added to the table?

#13

In the conversations_messages table there is dateline, it containes the php value of time()

#14

So, how can I do that?

#15

You are trying to get different pieces of information from different tables. I would probably create 2 queries. One over the conversations and the other over the messages. For each result, from the conversation table, run a SELECT over the messages table using the LIMIT function as others have suggested.

Now you have the title from the conversations table and the last message. Because you are trying to apply different limits on different tables this is the easiest method to achieve the results you are looking for.

#16

You’re right but if I display 20 conversations I need to run 20 extra queries in the page. I need to implement it in a website with ~30000 users online at the same time. Running all these queries will slow down the server.

#17

Now that I understand what you are after, can you post a database scheme?

#18

These are my tables:

  • conversations
    • cid int primary_key (id of the conversation)
    • subject varchar(255)
    • dateline int (creation date of the conversation)
  • conversations_users
    • cid int (id of the conversation)
    • uid int (id of the user)
    • lastread int (last time user read the conversation)
    • joindate int (time user joined conversation)
  • conversations_messages
    • mid int (id of the message)
    • cid int (id of the conversation)
    • uid int (id of the user)
    • message text
    • dateline int (creation time of the message)
#19

Any reason that column is not a datetime type, or an actual timestamp?

#20

I’m implementing it in a cms that uses php time() for dates, because it has its own functions to display dates in the page.

Sponsor our Newsletter | Privacy Policy | Terms of Service