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