我的 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
内部查询的被忽略了。)
对我在这里做错的事情有什么想法吗?