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