如何在属于已删除帖子的答案中过滤搜索? [英] How can I filter searching in answers that belong to a deleted post?

问题描述

我有一张这样的桌子:

// questions_and_answers
+----+---------------+------------------------+---------+---------+
| id |    subject    |           body         | related | deleted |
+----+---------------+------------------------+---------+---------+
| 1  | subject1      | question1              | NULL    | 0       |
| 2  |               | answer1                | 1       | 0       |
| 3  | subject2      | question2              | NULL    | 0       |
| 4  |               | answer2                | 3       | 1       |
| 5  |               | answer3                | 3       | 0       |
| 6  | subject3      | question3              | NULL    | 1       |
| 7  |               | answer4                | 6       | 0       |
+----+---------------+------------------------+---------+---------+
-- related column contains either NULL for questions or the id of its question for answers

如上表所示，answer2 和 question3 都被删除了.所以我想在搜索中过滤它们.关键是，我也想过滤 answer4.因为它属于已删除的问题(question3).知道如何进行此类过滤吗?

As you can see in table above, both answer2 and question3 are deleted. So I want to filter them in the searches. The point is, I want to filter answer4 either. Because it belongs to a deleted question (question3). Any idea how can I do such filtering?

这是我当前的查询:

SELECT qa.*
FROM questions_and_answers AS qa
WHERE MATCH(subject,body) AGAINST (:entry)
  AND deleted = 0
ORDER BY id DESC
LIMIT :page, 10;

如您所见，它仅过滤已删除的帖子.它不关心过滤属于已删除问题的答案.

As you can see, it only filters deleted posts. It doesn't care about filtering the answers that are belong to a deleted question.

推荐答案

如果只有一层关系——对于任何行 r 另一行 r' 存在，所以 r'.related = r.id, r.related IS NULL -- 您可以将问题加入答案并检查问题的 deleted 或使用 NOT EXISTS 进行检查，不存在已删除的父记录.

If there is only one level of relation -- for any row r for which another row r' exists, so that r'.related = r.id, r.related IS NULL -- you can either left join the questions to the answers and check the question's deleted or use a NOT EXISTS to check, that no deleted parent record exists.

LEFT JOIN 变体:

SELECT qa1.*
       FROM questions_and_answers qa1
            LEFT JOIN questions_and_answers qa2
                      ON qa2.id = qa1.related
       WHERE MATCH(qa1.subject, qa1.body) AGAINST (:entry)
             AND qa1.deleted = 0
             AND coalesce(qa2.deleted, 0) = 0
       ORDER BY qa1.id DESC
       LIMIT :page, 10;

NOT EXISTS 变体:

SELECT qa1.*
       FROM questions_and_answers qa1
       WHERE MATCH(qa1.subject, qa1.body) AGAINST (:entry)
             AND qa1.deleted = 0
             AND NOT EXISTS (SELECT *
                                    FROM questions_and_answers qa2
                                    WHERE qa2.id = qa1.related
                                          AND qa2.deleted = 1)
       ORDER BY qa1.id DESC
       LIMIT :page, 10;

这篇关于如何在属于已删除帖子的答案中过滤搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！