Использование UNNEST с JOIN

Я хочу иметь возможность использовать функцию unnest() в PostgreSQL в сложном SQL-запросе, который имеет много JOIN. Вот пример запроса:

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id
FROM mentions
INNER JOIN taggings ON taggings.mention_id = mentions.id
INNER JOIN tags ON tags.id = taggings.tag_id
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3
GROUP BY tags.parent_id

Я хочу исключить здесь таблицу taggings, потому что в моей таблице mentions есть поле целочисленный массив с именем taglist, которое состоит из всех связанных идентификаторов тегов mentions.

Я пробовал следующее:

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id 
FROM mentions 
INNER JOIN tags ON tags.id IN (SELECT unnest(taglist))
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3 
GROUP BY tags.parent_id

Это работает, но дает другие результаты, чем первый запрос.

Итак, что я хочу сделать, так это использовать результат SELECT unnest(taglist) в запросе JOIN, чтобы компенсировать таблицу taggings.

Как я могу это сделать?

ОБНОВЛЕНИЕ: taglist — это тот же набор, что и соответствующий список идентификаторов тегов упоминания.


postgresql sql join naming-conventions unnest
person İlker İnanç    schedule 17.04.2013    source источник
comment
mentions table has an integer array field named taglist that includes tag ids of mention. Укажите, что включает. Является ли taglist точно таким же набором, что и соответствующий список идентификаторов тегов упоминания?   -  person Erwin Brandstetter    schedule 18.04.2013
comment
Да, taglist — это тот же набор, что и соответствующий список идентификаторов тегов упоминания. Я обновляю свой вопрос.   -  person İlker İnanç    schedule 18.04.2013

Ответы (2)


arrow_upward
8
arrow_downward

Технически ваш запрос может работать так (не совсем уверен в цели этого запроса):

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT  unnest(m.taglist) AS tag_id
    FROM    mentions m
    WHERE   m.search_id = 3
    AND     9 = ANY (m.taglist)
    ) m 
JOIN   tags t  USING (tag_id) -- assumes tag.tag_id!
GROUP  BY t.parent_id;

Однако, мне кажется, вы идете в неправильном направлении здесь. Обычно можно удалить избыточный массив taglist и сохранить нормализованную схему базы данных. Тогда ваш исходный запрос должен хорошо работать, только укороченный синтаксис с псевдонимами:

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM   mentions m
JOIN   taggings mt ON mt.mention_id = m.id
JOIN   tags     t  ON t.id = mt.tag_id
WHERE  9 = ANY (m.taglist)
AND    m.search_id = 3
GROUP  BY t.parent_id;

Разгадать тайну

<rant> Коренной причиной ваших "разных результатов" является неудачное соглашение об именах, которое некоторые интеллектуально неполноценные ORM навязывают людям.
Я говорю о id как об имени столбца. Никогда не используйте этот анти-шаблон в базе данных с более чем одной таблицей. Верно, это означает практически любую базу данных. Как только вы соедините группу таблиц (это то, что вы делаете в базе данных), вы получите набор столбцов с именем id. Совершенно бессмысленно.
Столбец ID таблицы с именем tag должен быть tag_id (если нет другого описательного имени). Никогда id. </rant>

Ваш запрос непреднамеренно считает tags вместо mentions:

SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT unnest(m.taglist) AS id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t USING (id)
GROUP  BY t.parent_id;

Это должно работать следующим образом:

SELECT 25 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id
FROM  (
    SELECT m.id, unnest(m.taglist) AS tag_id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t ON t.id =  m.tag_id
GROUP  BY t.parent_id;

Я также добавил обратно DISTINCT к вашему count(), которое потерялось в вашем запросе.

person Erwin Brandstetter    schedule 17.04.2013
comment
Когда я пытаюсь выполнить ваш первый запрос, я получаю следующую ошибку: ERROR: column "tag_id" specified in USING clause does not exist in right table - person İlker İnanç; 18.04.2013
comment
ОК, я изменил код на следующий, и он работает: SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id FROM ( SELECT unnest(m.taglist) AS id FROM mentions m WHERE m.search_id = 4 AND 25 = ANY (m.taglist) ) m JOIN tags t USING (id) GROUP BY t.parent_id; Но он снова генерирует результаты, отличные от исходного запроса. - person İlker İnanç; 18.04.2013
comment
Хорошее объяснение. Спасибо за ваше время. - person İlker İnanç; 24.04.2013

arrow_upward
2
arrow_downward

Что-то вроде этого должно работать:

...
tags t INNER JOIN
(SELECT UNNEST(taglist) as idd) a ON t.id = a.idd
...
person Wojtas    schedule 17.04.2013