时间戳

首页 > 解决方案 > 如何根据标签过滤帖子？

mysql - 如何根据标签过滤帖子？

问题描述

这是我的表结构：

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

// tags
+----+--------+
| id |  name  |
+----+--------+
| 1  | mysql  |
| 2  | php    |
+----+--------+

// pivot 
+-------------+--------+
| question_id | tag_id |
+-------------+--------+
| 1           | 1      |
| 3           | 1      |
| 3           | 2      |
| 6           | 1      |
+-------------+--------+

我需要搜索带有特定标签的问题（所有答案）。例如，这是tag和entry的预期结果：[php]test

| 3  | subject2      | question2 test         | NULL    | 0       |
| 5  |               | answer3   test         | 3       | 0       |

所以首先我需要根据标签过滤帖子。对于[php]标签，这是正确的过滤：

| 3  | subject2      | question2 test         | NULL    | 0       |
| 4  |               | answer2                | 3       | 1       |
| 5  |               | answer3   test         | 3       | 0       |

然后根据条目过滤结果。对于test入口，这是正确的过滤：（与预期结果相同）

| 3  | subject2      | question2 test         | NULL    | 0       |
| 5  |               | answer3   test         | 3       | 0       |

这是我当前的查询，它不支持标签过滤。如何添加标签过滤？

SELECT COALESCE(qa2.subject, qa.subject) subject,
       qa.body
FROM questions_and_answers qa
LEFT JOIN questions_and_answers qa2 ON qa.related = qa2.id
WHERE MATCH(qa.subject,qa.body) AGAINST (:entry)

标签： mysqlsql

解决方案

试试这个脚本：

select distinct COALESCE(T2.id,T1.id) question_id
    ,COALESCE(T2.subject,T1.subject) subject
    ,COALESCE(T2.body,T1.body) body 
    ,T4.name tagName
from (
  ##To get body and filter body contains [test]   
  select * from questions_and_answers
  where body like '%test%'
) T1
##To get tag and filter tagName [php] 
left join questions_and_answers T2 on T1.related = T2.id
left join pivot  T3 on T3.question_id  = COALESCE(T2.id,T1.id)
left join tags T4 on T4.id = T3.tag_id
where T4.name like '%php%'

结果：

| question_id |  subject |           body | tagName |
|-------------|----------|----------------|---------|
|           3 | subject2 | question2 test |     php |

SQL Fiddle 演示测试链接

推荐阅读