How to select articles by multiple tags in order of highest match?

Question

SQL

There is a fairly typical database structure with articles:

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

Imagine that it contains the following data:

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

Now let's imagine that we need to get articles associated with tags from the list id: 10, 11, 12.
A simple query like this:

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

It will return to us Статья_1 и Статья_2, when necessary, so that the results are sorted by the number of tag matches (first Article_2, since it has 3 tags matched, and then Article_1 with one match).
I understand what needs to be added to the sample COUNT() as countand done ORDER BY count, but I don’t catch what exactly needs to be calculated.

Answer 1

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