Re: Foreign key joins revisited

On Sun, Dec 26, 2021, at 19:52, Joel Jacobson wrote:
> LEFT JOIN role r ON KEY p.permission_role_id_fkey

Ops! I see this doesn't quite work.
We're missing one single bit of information.
That is, we need to indicate if the foreign key is
a) in the table we're currently joining
or
b) to some existing table we've already joined in

Here comes a new proposal:

join_type from_item ON KEY foreign_key_constraint_name [IN referencing_table_alias | TO referenced_table_alias]

ON KEY foreign_key_constraint_name IN referencing_table_alias
- The foreign key is in a table we've already joined in, as given by referencing_table_alias.

ON KEY foreign_key_constraint_name TO referenced_table_alias
- The foreign key is in the table we're currently joining, and the foreign key references the table as given by referenced_table_alias. It's necessary to specify the alias, because the table referenced by the foreign key might have been joined in multiple times as different aliases, so we need to specify which one to join against.

Example:

FROM permission p
LEFT JOIN role r ON KEY permission_role_id_fkey IN p
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
LEFT JOIN team t ON KEY team_role_team_id_fkey IN tr
LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
LEFT JOIN "user" u ON KEY user_role_user_id_fkey IN ur

Thoughts?

/Joel