Início / dba / Perguntas / 222442
Accepted
laurent
Asked: 2018-11-14 07:42:39 +0800 CST

Impor dependência funcional conhecida em uma cláusula group by

  • 772

Vamos considerar que tenho as seguintes tabelas:

    CREATE TABLE users(
        id serial PRIMARY KEY,
        age integer
    )

    CREATE TABLE products(
        id serial PRIMARY KEY,
        sku character varying(255),
        user_id integer REFERENCES users
    )

Então, basicamente, um produto pertenceria a um usuário e um usuário poderia ter muitos produtos.

Eu quero a capacidade de carregar usuários com seus produtos fazendo a agregação no nível do banco de dados (talvez erroneamente, mas suponho que seria mais fácil e provavelmente mais eficiente do que executar algum código de agregação na minha camada de aplicativo: Postgres vem com boas funções de agregação, por que não usando-os !)

Então eu executaria algo como

SELECT "users".*, json_agg("products".*) as "products" 
  FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id" 
  GROUP BY "users"."id"

Tudo bem, meu driver pode analisar estruturas JSON e não tenho nada para fazer no lado da camada do aplicativo.

O problema surge se eu quiser introduzir algum tipo de paginação:

WITH "users" AS (SELECT * FROM "users" ORDER BY "id" LIMIT 20)
SELECT "users".*, json_agg("products".*) as "products" 
FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id" 
GROUP BY "users"."id"

Eu tenho o erro conhecido

ERRO: a coluna "users.age" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada

Como o "users"."id" não é considerado a chave primária da tabela temporária criada pela minha subconsulta

Eu poderia corrigi-lo adicionando todas as colunas de usuários na cláusula group by. Mas acho isso problemático e uma pena, pois tenho certeza de que "users".."id" definirá de maneira única um item da minha subconsulta.

Então, eu gostaria de saber se existe uma maneira de dizer ao mecanismo de banco de dados "usuários".."id" é algum tipo de chave primária para minha subconsulta?

Se não, você vê uma maneira melhor?

EDIT: Esta pergunta é bastante semelhante (3 anos de idade)

Obrigado

postgresql group-by

1 respostas

  1. Best Answer
    create table users (user_id int, user_name text);

insert into users values
(1, 'user1'),(2, 'user2'),(3, 'user3'),(4, 'user4'),(5, 'user5');

create table products (user_id int, id int, name text, att1 text);

insert into products values
(2, 1, 'prod1', 'att1'),
(2, 2, 'prod2', 'att1'),
(2, 3, 'prod3', 'att1'),
(2, 4, 'prod4', 'att1'),
(3, 5, 'prod5', 'att1'),
(3, 6, 'prod6', 'att1');

    IMHO sua primeira consulta deve lançar a mesma mensagem de erro.

    select
    users.*,
    json_agg(products.*) as products
from
    users
left join products on
    users.user_id = products.user_id;

    ERRO: a coluna "users.user_id" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada LINE 2: users.*,
    ^

    Mas você pode mover json_aggpara uma subconsulta em vez de juntar as duas tabelas, então você pode facilmente limitar o número de linhas retornadas.

    select 
    users.*,
    (select 
        json_agg(products.*)
     from
        products
     where
        user_id = users.user_id)  as products
from
    users
limit 3;

+---------+-----------+------------------------------------------------------+
| user_id | user_name | products                                             |
+---------+-----------+------------------------------------------------------+
| 1       | user1     |                                                      |
+---------+-----------+------------------------------------------------------+
| 2       | user2     | [{"user_id":2,"id":1,"name":"prod1","att1":"att1"},  |
|         |           |  {"user_id":2,"id":2,"name":"prod2","att1":"att1"},  |
|         |           |  {"user_id":2,"id":3,"name":"prod3","att1":"att1"},  |
|         |           |  {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+
| 3       | user3     | [{"user_id":3,"id":5,"name":"prod5","att1":"att1"},  |
|         |           |  {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+

    db<>fique aqui

    • 0

relate perguntas