Início / dba / Perguntas / 340510
Accepted
Luke Hutchison
Asked: 2024-06-26 05:01:51 +0800 CST

Escopo do alias de coluna no Postgres?

  • 772

Tenho a seguinte estrutura de tabela em meu banco de dados Postgres: tabela chat_channelpossui uma coluna otherUserIdque pode ser unida com tabela a coluna userIdde outra tabela, profile. A tabela profilepossui colunas userIdque podem ser unidas à coluna senderUserIdou recipientUserIdna tabela note.

Para qualquer linha que chat_channelcorresponda a determinados critérios de consulta, desejo extrair a única linha de profileonde chat_channel."otherUserId" = profile."userId"(como colunas extras retornadas da consulta). Também quero extrair colunas da linha correspondente notepara cada uma das duas userIdcorrespondências possíveis: where profile."userId" = note."senderUserId"e where profile."userId" = note."recipientUserId".

Estou tendo problemas para descobrir como criar um alias adequado de colunas para essa consulta duplamente aninhada, visto que a consulta interna ingressa na mesma tabela duas vezes.

Tenho que criar um alias para as colunas, pois estou retornando colunas da mesma tabela duas vezes nas linhas de resultados. Além disso, as tabelas compartilham colunas com o mesmo nome (principalmente id).

Tentativa (1):

SELECT    "chat_channel"."id" "chat_channel.id",
          "chat_channel"."channelId" "chat_channel.channelId",
          "chat_channel"."userId" "chat_channel.userId",
          "chat_channel"."otherUserId" "chat_channel.otherUserId",
          "chat_channel"."sortByDateTime" "chat_channel.sortByDateTime",
          "profile_other_user"."userId" "profile_other_user.userId",
          "profile_other_user"."name" "profile_other_user.name",
          "note_viewer_sent"."id" "note_viewer_sent.id",  -- (a)
          "note_viewer_sent"."senderUserId" "note_viewer_sent.senderUserId",
          "note_viewer_sent"."recipientUserId" "note_viewer_sent.recipientUserId",
          "note_viewer_sent"."noteText" "note_viewer_sent.noteText",
          "note_viewer_received"."id" "note_viewer_received.id",
          "note_viewer_received"."senderUserId" "note_viewer_received.senderUserId",
          "note_viewer_received"."recipientUserId" "note_viewer_received.recipientUserId",
          "note_viewer_received"."noteText" "note_viewer_received.noteText"
FROM      "chat_channel"
LEFT JOIN LATERAL (
    SELECT    "profile_other_user"."id",
              "profile_other_user"."userId",
              "profile_other_user"."name"
    FROM      "profile" "profile_other_user"
    LEFT JOIN LATERAL (
        SELECT "note_viewer_sent"."id",
               "note_viewer_sent"."senderUserId",
               "note_viewer_sent"."recipientUserId",
               "note_viewer_sent"."noteText"
        FROM   "note" "note_viewer_sent"  -- (b)
        WHERE  "note_viewer_sent"."recipientUserId" = "profile_other_user"."userId"
                AND "note_viewer_sent"."senderUserId" = $viewerUserId
        LIMIT 1
    ) AS "note_viewer_sent" ON TRUE  -- (c)
    LEFT JOIN LATERAL (
        SELECT "note_viewer_received"."id",
               "note_viewer_received"."senderUserId",
               "note_viewer_received"."recipientUserId",
               "note_viewer_received"."noteText"
        FROM   "note" "note_viewer_received"
        WHERE  "note_viewer_received"."senderUserId" = "profile_other_user"."userId"
                AND "note_viewer_received"."recipientUserId" = $viewerUserId
        LIMIT 1
    ) AS "note_viewer_received" ON TRUE
    WHERE "chat_channel"."otherUserId" = "profile_other_user"."userId"
    LIMIT 1
) AS "profile_other_user" ON TRUE
WHERE "chat_channel"."userId" = $viewerUserId
ORDER BY  "chat_channel"."sortByDateTime" DESC

Problemas com isso:

  • Isso dá ERROR: missing FROM-clause entry for table "note_viewer_sent"na linha marcada -- (a).
  • Tentar criar um alias para a tabela ( -- (b)) não resolve o problema.
  • Tentar criar um alias para os resultados da consulta mais profunda ( -- (c)) não resolve o problema.

Tentativa (2):

No entanto, se eu fizer a FROMcláusula de nível superior

FROM      "chat_channel", "note" "note_viewer_sent", "note" "note_viewer_received"

então o erro não é mais gerado, mas as WHEREcláusulas mais internas parecem ser ignoradas:

WHERE  "note_viewer_sent"."recipientUserId" = "profile_other_user"."userId"
       AND "note_viewer_sent"."senderUserId" = $viewerUserId

Especificamente, cada linha do conjunto de resultados contém colunas da mesma linha (errada) de notes, para a qual a WHEREcondição nem sequer é válida.

Além disso, parece que o efeito padrão de colocar várias tabelas FROMé obter um produto cruzado das tabelas. (Não posso reproduzir isso agora, mas a certa altura eu estava recebendo uma cópia de cada linha de profilepara cada linha de chat_channele as LIMIT 1consultas internas estavam sendo ignoradas.)

Alguma idéia sobre o que estou fazendo de errado aqui?

postgresql

1 respostas

  1. Best Answer

    A causa imediata da mensagem de erro é que você não pode fazer referência a nomes de colunas de saída de subconsultas aninhadas na SELECTlista externa. Poderia ser corrigido como:

    SELECT    "chat_channel"."id" "chat_channel.id",
       -- ...
FROM      "chat_channel"
LEFT JOIN LATERAL (
    SELECT *  -- !!!
    FROM   "profile" "profile_other_user"
    ...
    ) AS "profile_other_user" ON TRUE

    Ou melhor:

    SELECT c.id AS chat_channel_id,
    -- ...
       p.name AS profile_other_user_name,
       p.note_viewer_sent_id,      -- !
    -- ... 
       p.note_viewer_received_id,  -- !
    -- ...
FROM   chat_channel c
LEFT   JOIN LATERAL (
   SELECT *  -- !!!
   FROM   profile p
   LEFT   JOIN LATERAL (
      SELECT n.id AS note_viewer_sent_id  -- distinct alias
          -- ...
      FROM   note n
      WHERE  n."recipientUserId" = p."userId"  -- are you sure you ...
      AND    ...
      ) ns ON true
   LEFT   JOIN LATERAL (
      SELECT n.id AS note_viewer_received_id  -- distinct alias
          -- ...
      FROM   note n
      WHERE  n."senderUserId" = p."userId"  -- ... did not confuse sender/receiver here?!
      AND    ...
   -- ORDER  BY ???
      LIMIT  1
      ) nk ON true
   WHERE  p."userId" = c."otherUserId"
-- ORDER  BY ???
   LIMIT  1
   ) p ON true
-- ...

    Isso passa por todas as colunas da FROMlista da subconsulta. Você deve distribuir aliases de coluna exclusivos nas subconsultas aninhadas, é claro, e referenciar o alias da tabela pna SELECTlista externa.

    Há uma lista de outros problemas aqui ...

    • Use exclusivamente identificadores legais, em letras minúsculas e sem aspas, para que você não precise usar aspas duplas. Ver:

    • Em particular, nunca inclua pontos nos identificadores entre aspas. Isso é realmente tentar tornar sua vida difícil.

    • Não omita a ASpalavra-chave para aliases de coluna.
      Mas não há problema em omitir a ASpalavra-chave para aliases de tabela.
      Você entende o contrário. Relacionado:

    • LIMITsem determinístico ORDER BYretorna linhas arbitrárias. Não está claro se você deseja isso ou se precisa mesmo LIMITpara começar.

    • Pelo menos um nível LATERALé supérfluo.

    • Se a coluna puder ser nula, você provavelmente NULLS LASTdesejará

      ORDER BY "chat_channel"."sortByDateTime" DESC NULLS LAST

      Ver:

    O problema com nomes de colunas duplicados é agravado pelo antipadrão generalizado de usar "id" como nome de coluna em todas as tabelas. Não desaconselho, mas essa é apenas a minha opinião, ao contrário dos itens acima, que não são uma questão de opinião - na minha opinião. :) Mais sobre isso:

    • 3

relate perguntas