У меня есть набор городов, которые имеют отношение «многие ко многим» с набор тегов. Пользователь дает мне набор тегов (, который может содержать дубликаты! ), и мне нужно вернуть список совпадающих записей, отсортированных по релевантности.

Данные

Вот несколько примеров данных, чтобы проиллюстрировать проблему:

Города:

--------------------
| id |    city     |
--------------------
|  1 |  Atlanta    |
|  2 |  Baltimore  |
|  3 |  Cleveland  |
|  4 |  Denver     |
|  5 |  Eugene     |
--------------------

Теги:

------
| id |
------
|  1 |
|  2 |
|  3 |
|  4 |
------

Города помечены следующим образом:

Atlanta:   1, 2
Baltimore: 3
Cleveland: 1, 3, 4
Denver:    2, 3
Eugene:    1, 4

... поэтому таблица CityTags выглядит так:

------------------------
|  city_id  |  tag_id  |
------------------------
|     1     |     1    |
|     1     |     2    |
|     2     |     3    |
|     3     |     1    |
|     3     |     3    |
|     3     |     4    |
|     4     |     2    |
|     4     |     3    |
|     5     |     1    |
|     5     |     4    |
------------------------

Пример 1

Если пользователь дает мне идентификаторы тегов: [1, 3, 3, 4], я хочу подсчитать, сколько совпадений у меня есть для каждого из тегов, и вернуть результат с сортировкой по релевантности, например :

------------------------
|    city    | matches |
------------------------
|  Cleveland |    4    |
|  Baltimore |    2    |
|  Eugene    |    2    |
|  Atlanta   |    1    |
|  Denver    |    1    |
------------------------

Так как Кливленд сопоставил все четыре тега, он первым, за ним следуют Балтимор и Юджин, у каждого из которых было сопоставлено по два тега, и т. Д.

Пример 2

Еще один пример для хорошей оценки. Для поиска [2, 2, 2, 3, 4] мы получим:

------------------------
|    city    | matches |
------------------------
|  Denver    |    4    |
|  Atlanta   |    3    |
|  Cleveland |    2    |
|  Baltimore |    1    |
|  Eugene    |    1    |
------------------------

SQL

Если я проигнорирую повторяющиеся теги, то это тривиально:

SELECT name,COUNT(name) AS relevance FROM
  (SELECT name FROM cities,citytags 
    WHERE id=city_id AND tag_id IN (1,3,3,4)) AS matches
  GROUP BY name ORDER BY relevance DESC;

Но это не то, что мне нужно. Мне нужно уважать дубликаты. Может кто-нибудь подсказать, как я могу это сделать?

Решение в Postgresql

Ага! Мне нужна временная таблица. Postgresql позволяет мне делать это с помощью синтаксиса WITH. Вот решение:

WITH search(tag) AS (VALUES (1), (3), (3), (4))
SELECT name, COUNT(name) AS relevance FROM cities
INNER JOIN citytags ON cities.id=citytags.city_id
INNER JOIN search ON citytags.tag_id=search.tag
GROUP BY name ORDER BY relevance DESC;

Большое спасибо тем, кто ответил.