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