Scope of column aliasing in Postgres?

Question

I have the following table structure in my Postgres database: table chat_channel has a column otherUserId that can be joined with table the column userId of another table, profile. The table profile has column userId that can be joined with either the column senderUserId or recipientUserId in the table note.

For any row of chat_channel that matches given query criteria, I want to pull in the single row of profile where chat_channel."otherUserId" = profile."userId" (as extra columns returned from the query). I also want to pull in columns for the corresponding row in note for each of the two possible userId matches: where profile."userId" = note."senderUserId" and where profile."userId" = note."recipientUserId".

I am having trouble figuring out how to properly alias columns for this doubly-nested query, given that the inner query joins the same table twice.

I have to alias the columns, because I am returning columns from the same table twice in the result rows. Also, the tables share columns with the same name (notably id).

Attempt (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

Problems with this:

• This gives ERROR: missing FROM-clause entry for table "note_viewer_sent" on the line marked -- (a).
• Attempting to try to alias the table (-- (b)) does not solve the problem.
• Attempting to try to alias the results of the deepest query (-- (c)) does not solve the problem.

Attempt (2):

However, if I make the toplevel FROM clause

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

then the error is no longer generated, but the innermost WHERE clauses seem to be ignored:

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

Specifically, every row of the result set contains columns from the same (wrong) row of notes, for which the WHERE condition doesn't even hold.

Also, it seems that the default effect of putting multiple tables in FROM is to take a cross-product of the tables. (I can't reproduce this right now, but at one point I was getting a copy of every row of profile for every row of chat_channel, and the LIMIT 1 of the inner queries was being ignored.)

Any ideas about what I'm doing wrong here?

• Luke Hutchison: @Vérace The column names are derived from identifiers in Dart, which are supposed to use camelCase, hence the need for quoting them. The table names are already in snake_lower_case, but the query generation library quotes them all anyway (I didn't go through and remove all the unnecessary quotes).

Answer 1

The immediate cause for the error message is that you cannot reference output column names of nested subqueries in the outer SELECT list. Could be fixed like:

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

Or rather:

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
-- ...

This passes on all columns in the FROM list of the subquery. You must deal out unique column aliases in the nested subqueries, of course, and reference the table alias p in the outer SELECT list.

There is a list of other issues here ...

• Use legal, lower-case, unquoted identifiers exclusively, so you don't have to use double-quotes. See:

  • Are PostgreSQL column names case-sensitive?

• In particular, never include dots in quoted identifiers. That's really trying to make your life hard.

• Don't omit the AS keyword for column aliases.
  But it's ok to omit the AS keyword for table aliases.
  You have it the other way round. Related:

  • Aliases with correlated subqueries

• LIMIT without deterministic ORDER BY returns arbitrary rows. It's unclear whether you want that, or whether you even need LIMIT to begin with.

• At least one level of LATERAL is superfluous.

• If the column can be null, you probably want NULLS LAST in

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

  See:

  • Sort by column ASC, but NULL values first?

The issue with duplicate columns names is aggravated by the wide-spread anti-pattern of using "id" as column name in all tables. I advise against it, but that's just my opinion, as opposed to the items above, which are not a matter of opinion - in my opinion. :) More on that:

• How to implement a many-to-many relationship in PostgreSQL?