Select the first result of each conversation

Question

How can I select the first line of each conversation from a specific user where the to_id = 1.

The big problem is when the first message exchanged from the conversation does not have the top_id = 1 and ends up listing the next message of the conversation that has the from_id = 1, when you shouldn’t have.

Here is the link to the SQLFIDDLE: http://www.sqlfiddle.com/#! 9/7a772b/4

In SQLFIDDLE you are listing the Test 1 and the Test 4 when you should be listing only the Test 1, why the conversation Test 4 begins in the Test 3 where there is no to_id = 1.

SQL

SELECT t1.*, m2.message, m2.from_id FROM
    (SELECT to_id,message, MIN(created_at) AS created_at FROM messages m
    WHERE to_id = 1
    GROUP BY to_id,message) AS t1
INNER JOIN messages m2 ON t1.created_at = m2.created_at

Answer 1

Try this:

SELECT m1.to_id, m1.message, m1.created_at, m1.from_id
FROM messages m1
WHERE m1.to_id = 1
AND m1.created_at <= IFNULL(
                      (SELECT m2.created_at
                      FROM messages m2
                      WHERE (m2.from_id = m1.to_id AND m2.to_id = m1.from_id)
                      OR (m2.from_id = m1.from_id AND m2.to_id = m1.to_id)
                      LIMIT 1)
                    , NOW())
GROUP BY m1.to_id, m1.created_at

Answer 2

I did it the way I thought was right, I tested it here and it worked perfectly.

SELECT 
  t1.* 
FROM
  (SELECT * FROM messages) t1 
  INNER JOIN messages t2 
    ON (t2.id = t1.from_id AND t2.from_id = t1.id)
WHERE t1.to_id = 1

Answer 3

The query is well planned, the problem is in the organization of it.

Your current query (Sqlfiddle) looks like this

SELECT t1.*, m2.message, m2.from_id FROM
    (SELECT to_id,message, MIN(created_at) AS created_at FROM messages m
    WHERE to_id = 1
    GROUP BY to_id,message) AS t1
INNER JOIN messages m2 ON t1.created_at = m2.created_at

You’re going through so much id how much message as a reference to the GROUP BY which ironically has no logic.

To work just remove message of GROUP BY, leaving your query like this

SELECT t1.*, m2.message, m2.from_id FROM
    (SELECT to_id,message, MIN(created_at) AS created_at FROM messages m
    WHERE to_id = 1
    GROUP BY to_id) AS t1
INNER JOIN messages m2 ON t1.created_at = m2.created_at

Sqlfiddle: http://www.sqlfiddle.com/#! 9/7a772b/10