Free the joins! APIv4 explicit joins - can we remove the requirement for specific columns in the "on" clause?
API4 currently micromanages the join conditions of explicit joins, forcing us to use awkward workarounds if we want to join entities on arbitrary conditions.
For example, I'd like to be able to join Email to Email so I can find different contacts who have the same email addresses. The Email's id is irrelevant to the join; what's relevant are the "email" and "contact_id" fields. See https://civicrm.stackexchange.com/a/38496/446.
Currently, APIv4 enforces a requirement that explicit joins have an "id" column for the joined entity in one of the join's "on" conditions. This enforcement takes place in Api4SelectQuery::getJoinConditions(). If no "on" condition with a "joinEntity.id" column is explicitly given, the code tries to create a new "on" condition using a foreign key relationship from the entity's metadata.
I can trick APIv4 into doing what I want by specifying a gratuitous "on" condition of the form "joinEntity.id = joinEntity.id". See the SE link above for an example. But this smells bad to me. When it comes to explicit join conditions, can't we figure out a way to tell APIv4 "step back, I'll handle this"?