On Sun, 26 Dec 2021 at 14:37, Joel Jacobson <j...@compiler.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.
