Как выбрать статьи по нескольким тегам в порядке наибольшего совпадения?

Question

[gidificer]

Имеется довольно-таки типичная структура БД со статьями:

articles (id, body)
tags (id, name)
article_tags (id_article, id_tag)

Представим, что в ней есть следующие данные:

articles:
1   Статья_1
2   Статья_2
3   Статья_3

tags:
10   Тег_10
11   Тег_11
12   Тег_12
13   Тег_13

article_tags:
1   10
2   10
2   11
2   12

Теперь представим, что нам нужно получить статьи, привязанные к тегам из списка id: 10, 11, 12.

Простой запрос вида:

select * from articles 
where id in (select id_article from article_tags where id_tag in (10, 11, 12))

Вернет нам Статья_1 и Статья_2, когда как нужно, чтобы результаты были отсортированы по количеству совпадений тегов (сначала Статья_2, т. к. у неё 3 тега совпало, а потом уже Статья_1 с одним совпадением).

Понимаю, что нужно в выборку добавлять COUNT() as count и делать ORDER BY count, но не улавливаю, что именно нужно подсчитывать.

Answer 1

[Rsa97]

SELECT *
  FROM (
    SELECT `at`.`id_article` AS `id_article`, COUNT(*) AS `count`
      FROM `article_tags` AS `at`
      JOIN `tags` AS `t` ON `t`.`id` = `at`.`id_tag` 
        AND `t`.`name` IN ('Тег_10', 'Тег_11', 'Тег_12')
      GROUP BY `id_article`
  ) AS `c` 
  JOIN `articles` AS `a` ON `a`.`id` = `c`.`id_article`
  ORDER BY `c`.`count` DESC

Comments

[gidificer]

Спасибо! Я бы до такого запроса еще не скоро дошёл, работает идеально)

Answer 2

[Дмитрий Ковальский]

Если на вашей базе данных есть функционал CTE, то можно так попробовать

WITH articlesCount(articleId,count) as 
SELECT articleId, COUNT(tagId) as count from article_tags
GROUP BY articleId

SELECT what your need from article as a
INNER JOIN articlesCount as ac on a.id = ac.articleId
order by ac.count

Если нету - подобное можно провернуть через временные таблицы.

Comments

[gidificer]

Про CTE раньше не слышал, а временные таблицы стараюсь обходить стороной. У меня MS SQL и CTE там есть, но каким-то избыточным кажется для решения такой задачи

[Дмитрий Ковальский]

gidificer: Надо смотреть query plan. Такой запрос при всей "монструозности" может выполнится просто эффективнее чем вложенный. Инструмент CTE лучше освоить если планируете писать сложные запросы.