Real-world example: Someone wants Job-Application by Country, but that could mean via Applicant's Residence, the Applicant's Nationality, the Requisition Primary location, or one of the Requisition's Satellite Offices, etc.
... And god help you if someday Requisitions need to have Revisions too.
Yes, this is accurate. You often end up with many solutions and human judgement is sometimes required in the end to pick the right strategy. However, what I have found in practice is that heuristics and hinting can rapidly cut through that complexity. E.g. "always pick the shortest path between tables, and never use this set of tables as intermediate nodes on any path" rules out a ton of options, and usually will leave you with one or two, and usually those are the natural choices.
In this way you can use polynomially-many constraints or rules to avoid exponentially-many weird or exceptional routes through the schema. I am optimistic that you can build a system where by default, the autojoin solution is the natural one maybe 90% of the time. There will certainly be exceptions where you have to express the join conditions explicitly. But I think you can dramatically reduce the amount of code required.
I would also hazard the suggestion that this might produce productive backpressure on the system. If the autojoiner is struggling to find a good route through the schema, it's possible that the schema is not properly normalized or otherwise messed up.
I mean, I think the issue is just that there are lots of possible paths so it can't be automated.
If you want to join employees to buildings, is it where their desk is assigned today, or where their desk was assigned two years ago, or where their team is based even though they work remotely, or where they last badged in?
Sure you can build a tool to find all potential joins based on foreign keys, but then how do you know which is correct unless you understand what the tables mean? And then if you understand what the tables mean, writing the join out yourself is trivial.
> Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables.
In my experience, having just one or two ways is for simple/toy projects. Lots of joins in no way means a schema is "fucked up". It probably just means it's correctly modeling actual relationships, correctly normalized.