Re: Foreign key joins revisited

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

> Let's look at each row your example and see if we can work it out.
> I've added the "FROM permission p" and also "AS [table alias]",
> otherwise the aliases you use won't exist.
>
> > FROM permission p
>
> This row is obviously OK. We now have "p" in scope as an alias for
> "permission".
>
> > LEFT JOIN FOREIGN KEY p->permission_role_id_fkey AS r
>
> This row would follow the FK on "p" and join the "role" table using the
> "permission.role_id" column. OK.
>
> > LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey AS tr
>
> This is where we fail. There is no "tr" table alias yet! So we cannot
> follow the FK.
>
> The reason why it doesn't work is because the FK is:
> FOREIGN KEY team_role (role_id) REFERENCES role
>
> That is, the FK is on the new table we are currently joining in.
>

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.