Объединить две выборки из таблицы М:М с учётом count

Question

Есть три таблицы: арты, теги и связующая таблица для проставления тегов для каждого арта.

main_tags — все теги;

arts — арты;

tag_to_art — связь тегов и артов;

Часть данных из таблиц:

SELECT id, ru FROM main_tags ORDER BY id ASC LIMIT 5;

 id |       ru
----+-----------------
  2 | мужчина
  3 | девушка
  4 | длинные волосы
  5 | короткие волосы
  6 | на улице

SELECT id FROM arts;

 id
----
  4
  5

SELECT * FROM tag_to_art;

         tag          | art
----------------------+-----
 девушка              |   5
 девушка              |   4
 длинные волосы       |   5
 на улице             |   4

Выходит, что на арте с id 4 два тега: девушка и на улице, а на арте 5 - девушка и длинные волосы.

Вот так можно найти все теги, которые проставлены на отдельный арт по его id:

SELECT main_tags.id, tag_to_art.tag
FROM main_tags
JOIN tag_to_art ON main_tags.ru = tag_to_art.tag
WHERE tag_to_art.art = '4';

 id |         tag
----+----------------------
  6 | на улице
  3 | девушка

Таким образом можно найти сколько раз был привязан тег к разным артам:

SELECT COUNT(tag) FROM tag_to_art
WHERE tag = 'девушка';

 count
-------
     2

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

 id |         tag          | count
----+----------------------+-------
  4 | длинные волосы       |     1
  3 | девушка              |     2

Моя попытка:

    art_id = 4 # сюда приходит любой id по запросу

    cur.execute(f"""SELECT main_tags.id, tag_to_art.tag, COUNT(tag_to_art.tag) AS count
                FROM main_tags
                JOIN tag_to_art ON main_tags.ru = tag_to_art.tag
                WHERE tag_to_art.art = '{art_id}'
                GROUP BY main_tags.id, tag_to_art.tag
                ORDER BY count, date DESC
                """)

    data = cur.fetchall()

Увы, но таким образом почти всё верно, но подсчитывает только количество использования тега на конкретном арте, а не во всей таблице.

 id |         tag          | count
----+----------------------+-------
  4 | длинные волосы       |     1
  3 | девушка              |     1 # но здесь должно быть 2, оно подсчитало только на одном арте

Мне необходимо или используя один запрос (можно с подзапросами), или инструментами Python сделать на выходе необходимый вид.

Answer 1

SELECT t1.id, t2.tag, COUNT(*) AS "count"
FROM main_tags t1
JOIN tag_to_art t2 ON t1.ru = t2.tag
JOIN arts t3 ON t2.art = t3.id
GROUP BY t1.id, t2.tag
HAVING MAX((t2.art = 4)::INT) = 1;

id	tag	count
6	на улице	1
3	девушка	2

SELECT t1.id, t2.tag, COUNT(*) AS "count"
FROM main_tags t1
JOIN tag_to_art t2 ON t1.ru = t2.tag
JOIN arts t3 ON t2.art = t3.id
GROUP BY t1.id, t2.tag
HAVING MAX((t2.art = 5)::INT) = 1;

id	tag	count
3	девушка	2
4	длинные волосы	1

fiddle

БЛОГ НА HUSL

Объединить две выборки из таблицы М:М с учётом count

Ответы (1 шт):