Re: Foreign key joins revisited

On Sun, Dec 26, 2021, at 21:49, Isaac Morland wrote:
> Right, sorry, that was sloppy of me. I should have noticed that I wrote "tr-> ... AS tr". But in the case where the "source"
> (referencing) table is already in the join, what's wrong with allowing my suggestion? We do need another way of joining to
> a new table using one of its foreign keys rather than a foreign key on a table already in the join, but it seems the first case
> is pretty common.

I like your idea!
It's would be nice to avoid having to explicitly specify the referenced table, when simply following a foreign key on a table already in the join.

Before I read your reply, I sent a new message in this thread, suggesting a ON KEY ... [IN | TO] ... syntax.

I think if we combine the ON KEY ... TO ... part of my idea, with your idea, we have a complete neat solution.

Maybe we can make them a little more similar syntax wise though.

Could you accept "ON KEY" instead of "FOREIGN KEY" for your idea?
And would a simple dot work instead of ->?

We would then get:

FROM permission p
LEFT JOIN ON KEY p.permission_role_id_fkey r
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
LEFT JOIN ON KEY tr.team_role_team_id_fkey t
LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
LEFT JOIN ON KEY ur.user_role_user_id_fkey u

Simply following a foreign key on a table already in the join:
LEFT JOIN ON KEY p.permission_role_id_fkey r
Here, "p" is already in the join, and we follow the "permission_role_id_fkey" foreign key to "role" which we don't need to specify, but we do specify what alias we want for it, that is "r".

If instead joining to a new table using one of its foreign keys:
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
Here, we follow the foreign key on team_role named "team_role_role_id_fkey" and indicate we want to join against the table alias "r", which will then be asserted to actually be an instance of the "role" table. We need to specify the table alias, as we might have "role" in the join multiple times already as different aliases.

Thoughts?