Re: Foreign key joins revisited

On Sun, Dec 26, 2021, at 20:02, Isaac Morland wrote:
> Is it going too far to omit the table name? I mean, any given foreign key can only point to one other table:

That actually how I envisioned this feature to work way back, but it doesn't work, and I'll try to explain why.

As demonstrated, we can omit the referenced_table_alias, as it must either be the table we are currently joining, or is the table that the foreign key references.
But we are not always following foreign keys on tables we have already joined in.
Sometimes, we need to do the opposite, to follow a foreign key on a table we have not yet joined in, and the referenced table is instead a table we have already joined in.

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.

On the previous row, we followed a FK on "p" which was a table we had already joined in.

I hope this explains the problem.

/Joel