php - mysql - 返回分组在列中的结果

Question

我正在研究搜索/标签系统。我写的原始查询是针对我在文章/视频表中存储“标题”、“描述”和逗号分隔的“标签”列时的查询。从那以后，我意识到规范化我的标签的好处。我现在有三个表要处理...

tbl_文章

• 文章编号
• 标题
• 描述
• 内容

tbl_tag_index

• tag_id(替代主 ID)
• tag_type(对于 tbl_Articles 等于 1，2 个用于 tbl_videos)
• tag_word_id(见下表)
• tag_target_id(article_id/video_id -取决于标签类型)

tbl_tag_word

• tag_word_id
• tag_word(最后是实际标签)

此查询返回标签...唯一的问题是它将它们作为不同的行返回。我想我需要将结果分组到同一行，以便我的搜索查询可以工作

SELECT * 
  FROM `tbl_articles` A 
  JOIN `tag_index` I ON A.article_id = I.tag_target_id 
  JOIN tag_word W ON I.tag_word_id = W.tag_word_id 
 WHERE I.tag_type_id = 1

这是我以前的搜索查询

SELECT *, 
(
(CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `tags` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%dog%' THEN 1 ELSE 0 END)

) AS relevance
FROM `tbl_Articles`
WHERE `description` LIKE '%hotel%'
  OR `description` LIKE '%london%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%' 
  OR `title` LIKE '%hotel%'
  OR `title` LIKE '%london%'
  OR `title` LIKE '%lazy%'
  OR `title` LIKE '%dog%'
  OR `tags` LIKE '%hotel%'
  OR `tags` LIKE '%london%'
  OR `tags` LIKE '%lazy%'
  OR `tags` LIKE '%dog%'
ORDER BY relevance DESC
LIMIT 0 , 10;

Answer 1

使用:

SELECT A.*, 
       GROUP_CONCAT(DISTINCT w.tag_word ORDER BY w.tag_word ASC SEPARATOR ',') AS tags,
       COUNT(DISTINCT w.tag_word) +
       (CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +

       (CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
       (CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) AS relevance
  FROM tbl_articles A 
  JOIN tag_index I ON A.article_id = I.tag_target_id 
  JOIN tag_word W ON I.tag_word_id = W.tag_word_id 
 WHERE I.tag_type_id = 1
   AND w.tag_word IN ('hotel', 'london', 'lazy', 'dog')
GROUP BY a.article_id, a.title, a.description, a.content
ORDER BY relevance DESC