Select the first result of each conversation

Question

Select the first result of each conversation

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

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

The big problem is when the first message changed from the conversation does not have top_id = 1 and ends up listing the next message in the conversation that has from_id = 1 , when it should not.

Here is the link to SQLFIDDLE: link

In SQLFIDDLE you are listing Test 1 and Test 4 when you should be listing only Test 1 , why Test 4 conversation starts in Test 3 where to_id = 1 does not.

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

mysql sql

asked by anonymous 05.08.2018 / 22:30

3 answers

1

I did it the way I thought it 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

05.08.2018 / 23:36

0

The query is well planned, the problem is in its organization.

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 are passing both id and message as a reference to GROUP BY which ironically is not logical.

To work, just remove message from 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: link

05.08.2018 / 22:36

Problem in game startup What is a git diff, how to do it and what is it for?

score 1 · Accepted Answer

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