Re: Foreign key joins revisited

On Sun, 26 Dec 2021 at 01:47, Joel Jacobson <joel(at)compiler(dot)org> wrote:

> On Sat, Dec 25, 2021, at 21:55, Joel Jacobson wrote:
> > FROM permission p
> > LEFT JOIN role r WITH p->permission_role_id_fkey = r
> > LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
> > LEFT JOIN team t WITH tr->team_role_team_id_fkey = t
> > LEFT JOIN user_role ur WITH ur->user_role_role_id_fkey = r
> > LEFT JOIN "user" u WITH ur->user_role_user_id_fkey = u
> > WHERE p.id = 1;
>

Is it going too far to omit the table name? I mean, any given foreign key
can only point to one other table:

[....]
LEFT JOIN FOREIGN KEY p->permission_role_id_fkey
LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey
LEFT JOIN FOREIGN KEY tr->team_role_team_id_fkey
LEFT JOIN FOREIGN KEY ur->user_role_role_id_fkey
LEFT JOIN FOREIGN KEY ur->user_role_user_id_fkey
[....]

or some such; you can determine which other table is involved from the
foreign key.

Parenthetically, I'm going to mention I really wish you could us ON and
USING in the same join. USING (x, y, z) basically means the same as ON
((l.x, l.y, l.z) = (r.x, r.y, r.z)); so it's clear what putting them
together should mean: just take the fields listed in the USING and add them
to the ON clause in the same way as is currently done, but allow it even if
there is also an explicit ON clause.