Martin AJ Martin AJ - 3 months ago 10
SQL Question

How can I select the last edited version of the post?

I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1 | title1 | question content | NULL | 0 | NULL |
| 2 | | answer content | 1 | 1 | NULL |
| 3 | title2 | question content | NULL | 0 | NULL |
| 4 | | answer content | 3 | 1 | NULL |
| 5 | | answer content | 1 | 1 | NULL |
| 6 | | answer content (edited) | NULL | 1 | 2 |
| 7 | title3 | question content | NULL | 0 | NULL |
| 8 | title1 | question content (edited) | NULL | 0 | 1 |
| 9 | | answer content | 7 | 1 | NULL |
| 10 | title1 | question content (edited) | NULL | 0 | 1 |
| 11 | title3 | question content (edited) | NULL | 0 | 7 |
+----+---------+---------------------------+---------+------+-----------+


Column explanations:



related
column:



  • NULL
    for both questions and edited version of questions/answers

  • {the id of its own question}
    for answers



type
column:



  • 0
    for questions

  • 1
    for answers



edited_id
column:
(the id of original post)


  • NULL
    means it is a original question/answer

  • {any number}
    means it is a edited version of a question/answer.






Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).

Example1: I have this value:
:id = 1
and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1 | question content (edited) | NULL | 0 | 1 |
| 6 | | answer content (edited) | NULL | 1 | 2 |
| 5 | | answer content | 1 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+


Example2: I have this value:
:id = 3
and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3 | title2 | question content | NULL | 0 | NULL |
| 4 | | answer content | 3 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+


Example2: I have this value:
:id = 7
and I want this output:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3 | question content (edited) | NULL | 0 | 7 |
| 9 | | answer content | 7 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+





Here is my current query:

SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
(related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter


As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?

Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.

Answer

You can try the following query:

SET @qid := 1;

SELECT 
QA.*
FROM QA 
INNER JOIN 
(
    SELECT 
    MAX(GREATEST(A.id, COALESCE(B.id, 0))) latest_id
    FROM QA A 
    LEFT JOIN QA B ON A.id = B.edited_id 
    WHERE @qid IN(A.id,A.related,A.edited_id)
    GROUP  BY A.type , IF(A.type = 1, A.id,0)
) AS t 
ON QA.id = t.latest_id

WORKING DEMO