Início / dba / Perguntas / 173831
Accepted
Ced
Asked: 2017-05-18 07:15:53 +0800 CST

Converta o lado direito da junção de muitos para muitos em matriz

  • 772

Ao usar o relacionamento de muitos para muitos, o resultado é dividido em várias linhas. O que eu gostaria de fazer é converter o lado direito de uma junção em uma matriz para que o resultado seja uma linha.

Exemplo com 3 tabelas:

CREATE TABLE items (
  id    serial primary key,
  title text
);

CREATE TABLE tags (
  id    serial primary key,
  title text
);

CREATE TABLE items_tags (
  item_id int references items(id),
  tag_id  int references tags(id),
  primary key (item_id, tag_id)
);

Ao selecionar itens com suas tags, posso fazer assim:

SELECT i.id, i.title, i.title
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id;

E o resultado será:

(1, "item n1", "sport")
(1, "item n1", "soccer")
(2, "item n2", "adventure")
(2, "item n2", "mountain climbing")
(2, "item n2", "sport")
(2, "item n2", "nature")

O que eu gostaria de ter é isso:

(1, "item n1", ["sport", "soccer"])
(2, "item n2", ["adventure", "mountain climbing", "sport" , "nature"])
postgresql aggregate

2 respostas

  1. Best Answer

    Para agregar a maioria das linhas

    Ao consultar todos ou a maioria dos itens, normalmente é substancialmente mais rápido agregar linhas da tabela "muitos" primeiro e unir depois:

    SELECT id, i.title AS item_title, t.tag_array
FROM   items      i
JOIN  (  -- or LEFT JOIN ?
   SELECT it.item_id AS id, array_agg(t.title) AS tag_array
   FROM   items_tags it
   JOIN   tags       t  ON t.id = it.tag_id
   GROUP  BY it.item_id
   ) t USING (id);

    Use LEFT [OUTER] JOINna consulta externa se houver itens sem tags - que seriam excluídos com [INNER] JOIN.

    Como isso não multiplica as linhas na junção, não precisamos GROUP BYno exterior SELECT.

    A junção antes da agregação também fica fora de controle com mais de uma tabela 1:n na FROMlista (não neste caso simples). Ver:

    Para agregar algumas linhas

    Para uma pequena porcentagem de linhas, use uma LATERALjunção com um construtor ARRAY :

    SELECT id, title AS item_title, t.tag_array
FROM   items i, LATERAL (  -- this is an implicit CROSS JOIN
   SELECT ARRAY (
      SELECT t.title
      FROM   items_tags it
      JOIN   tags       t  ON t.id = it.tag_id
      WHERE  it.item_id = i.id
      ) AS tag_array
   ) t;

    Como um construtor ARRAY sempre produz uma linha (com array vazio se a subconsulta estiver vazia - diferença sutil no resultado!), LEFT JOIN LATERAL (...) ON truenão é necessário aqui. Ver:

    Aparte

    Você teve um erro de digitação na sua consulta. A 3ª coluna seria t.title. Adicionei aliases à sua consulta original (não agregada) para esclarecer:

    SELECT i.id, i.title AS item_title, t.title AS tag_title
FROM   items      i
JOIN   items_tags it ON it.item_id = i.id
JOIN   tags       t  ON t.id = it.tag_id;

    "id" ou "title" normalmente não são identificadores muito distintivos e não muito úteis. Ver:

    • 31

  2. Você precisa adicionar a group bycláusula e usar array_agg.

    SELECT i.id, i.title, array_agg(i.title)
FROM items i
INNER JOIN items_tags it
ON it.item_id = i.id
INNER JOIN tags t
ON t.id = it.tag_id
GROUP BY i.id, i.title,
    • 20

relate perguntas