CODEWITHSUNDEEP
phpmysqldatabase
D_R
D_R

Reputation: 4972

How can a pull message thread list data from database?

This is my MySQL DB structure:

Recipents (id, converstation_id, user_id)

Conversations (id, user_start)

Messages (id, user_id, message, conversation_id, time)

What I am trying to do is to pull all the conversations ids that were started by a specific user or he is the recipient of one of the conversations. And sort it by the last message.

How can I do that?

Upvotes: 1

Views: 134

Answers (1)

hek2mgl
hek2mgl

Reputation: 158080

The following query will work. You'll have to replace REQUESTED_USER_ID by a valid user id. Note the UNION statement in SQL that concats the result of the two sub queries. The result from the UNION is then wrapped in another SELECT to achieve the order by start time.

SELECT
  conversation_id
FROM (
  SELECT 
    c.id AS conversation_id,
    m.time AS start
  FROM Conversations c
  JOIN Messages m ON m.conversation_id = c.id
  WHERE c.user_start = REQUESTED_USER_ID
  UNION
  SELECT
    c.id AS conversation_id,
    m.time AS start
  FROM Conversations c
  JOIN Recipents r ON c.id = r.user_id
  JOIN Messages m ON m.conversation_id = c.id
  WHERE r.user_id = REQUESTED_USER_ID
) result

ORDER BY start DESC;

Upvotes: 1

Related Questions