我的 Postgres 数据库中有以下表结构:表chat_channel
有一个otherUserId
可以与表连接的列userId
,另一个表的列,profile
。表profile
有一个userId
可以与表中的列senderUserId
或连接的列。recipientUserId
note
对于符合给定查询条件的任何行,我都希望提取wherechat_channel
的单行(作为查询返回的额外列)。我还希望提取两个可能匹配项的相应行的列: where和 where 。profile
chat_channel."otherUserId" = profile."userId"
note
userId
profile."userId" = note."senderUserId"
profile."userId" = note."recipientUserId"
由于内部查询两次连接同一张表,所以我很难弄清楚如何为这个双重嵌套查询正确地添加别名列。
我必须为这些列添加别名,因为我在结果行中两次返回来自同一张表的列。此外,这些表共享具有相同名称的列(尤其是id
)。
尝试(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
存在的问题:
- 这给出了
ERROR: missing FROM-clause entry for table "note_viewer_sent"
标记的线-- (a)
。 - 尝试为表添加别名(
-- (b)
)并不能解决问题。 - 尝试对最深查询的结果进行别名化(
-- (c)
)并不能解决问题。
尝试(2):
但是,如果我制定 toplevelFROM
子句
FROM "chat_channel", "note" "note_viewer_sent", "note" "note_viewer_received"
那么错误不再产生,但是最里面的WHERE
子句似乎被忽略了:
WHERE "note_viewer_sent"."recipientUserId" = "profile_other_user"."userId"
AND "note_viewer_sent"."senderUserId" = $viewerUserId
具体来说,结果集的每一行都包含来自同一行(错误行)的列notes
,而该WHERE
条件甚至不成立。
此外,放入多个表的默认效果似乎FROM
是对表进行叉积。(我现在无法重现这一点,但有一次我得到了每一行的每一行的副本profile
,chat_channel
而LIMIT 1
内部查询的被忽略了。)
对我在这里做错的事情有什么想法吗?
导致该错误消息的直接原因是您无法在外部列表中引用嵌套子
SELECT
查询的输出列名称。可以这样修复:更确切地说:
这将传递子查询列表中的所有列。当然,您必须在嵌套子查询中处理唯一的列别名,并在外部列表中
FROM
引用表别名。p
SELECT
这里还有其他问题的列表...
仅使用合法的、小写的、不带引号的标识符,这样就不必使用双引号。请参阅:
尤其是,永远不要在引号内的标识符中包含点。这确实会让你的生活变得艰难。
不要省略
AS
列别名的关键字。但可以省略
AS
表别名的关键字。反过来也可以。相关:
LIMIT
不带确定性ORDER BY
返回任意行。不清楚您是否想要这样做,或者您是否需LIMIT
要这样做。至少有一个级别
LATERAL
是多余的。如果列可以为空,则您可能
NULLS LAST
希望看:
在所有表中使用“id”作为列名这一广泛存在的反模式加剧了重复列名的问题。我不建议这样做,但这只是我的观点,与上述观点相反,这不是一个意见问题 - 在我看来。:) 更多信息: