Двойные массивы на сложном соединении

postgresql aggregate-functions

20 просмотра

1 ответ

Я хочу создать денормализованный вид сложной структуры в моей базе данных postgresql. У меня есть основная сущность gamesи несколько отношений «один ко многим», таких как tags(два типа) и files. Проблема состоит в том, что массивы содержат элементы с одинаковыми идентификаторами независимо от distinctпредложения внутри подзапросов. Я сужу запрос к связанной части:

select
  array_to_json(array_remove(array_agg((
    select distinct t.id where t.type = 1
  )), null))::jsonb as genre_ids
from games g
left outer join game_tags gt on gt.game_id = g.id -- one-to-many
left outer join tags t on gt.tag_id = t.id
left outer join game_files gf on gf.game_id = g.id -- one-to-many as well
left outer join files f on gf.file_id = f.id
left outer join ratings r on r.game_id = g.id
group by g.id;

Допустим, у меня есть игра id=1и отношения

# game_tags (game_id, tag_id)
1, 100

# game_files (game_id, file_id)
1, 1000
1, 1001
1, 1002
1, 1003

Тогда в результате запроса я увижу массив [100, 100, 100, 100]. Та же ситуация, когда количество тегов больше единицы. Я не знаю, если это из-за неправильного раздела агрегации или из-за неправильной группировки. Я надеялся, что это distinctключевое слово поможет, но это не так. Конечно, я могу отфильтровать элементы перед отправкой на веб-интерфейс, но я считаю, что это неправильный путь.

Я придерживался pg 9.5.

Автор: Tommi Источник Размещён: 08.11.2019 11:20

Ответы (1)


1 плюс

Решение

Проблема в том, что вам нужно группировать по разным базам: один раз для игровых тегов и один раз для файлов. Вы можете решить это с помощью подзапросов в качестве источников строк. Группировка происходит в подзапросах, поэтому они не влияют друг на друга:

select g.*, t.genre_ids, f.file_ids
from games g
left join (
  select gt.game_id, jsonb_agg(t.id) as genre_ids
  from game_tags gt
  join tags t on gt.tag_id = t.id
  where t."type" = 1
  group by gt.game_id) t on t.game_id = g.id
left join (
  select game_id, jsonb_agg(file_id) as file_ids
  from game_files
  group by game_id) f on f.game_id = g.id
left join ratings r on r.game_id = g.id -- not used
;
Автор: Patrick Размещён: 20.08.2016 01:50
Вопросы из категории :
32x32