Re: Foreign key joins revisited

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;

Someone pointed out the part to the right of the last equal sign is redundant.

Also, "KEY" is perhaps a better keyword to use than "WITH",
to indicate it's a join using a foreign KEY.

With these two changes, the query becomes:

FROM permission p
LEFT JOIN role r KEY p->permission_role_id_fkey
LEFT JOIN team_role tr KEY tr->team_role_role_id_fkey
LEFT JOIN team t KEY tr->team_role_team_id_fkey
LEFT JOIN user_role ur KEY ur->user_role_role_id_fkey
LEFT JOIN "user" u KEY ur->user_role_user_id_fkey
WHERE p.id = 1;

/Joel