hmm okay. How do you mean with "way too complex"? I feel like this is a pretty standard setup/problem and is supported in, for example, Django with select_related/prefetch_related

… then a call to Book.objects.select_related('author__hometown').get(id=4) will cache the related Person and the related City:

Some kind of recursive call with a base case that falls back to lazy loading/fetching reference for cyclic relations might be one way to go about it. But I can see how it can quickly escalate in complexity.

OTOH, running a subquery can be a workaround but that makes the application code messy quickly too.

+1 for me

It's very common to have tables that exist primarily of a set of IDs referencing other tables, and you want to rebuild a view of the original data. E.g., a tables such as:

events

purchasers

items

If you were selecting by event_id, you'd likely immediately want access to the records from the items and purchasers table. But, at present, you have to pick one of these to fit into a SelectTwo<E, F>, and then perform a second query to obtain the purchaser record.

Other ORMs I've worked with implement methods (in this example) on the Event model that allow fetching the related records (potentially referencing cached values). E.g., while there would be an Event.item_id structure member, an Event.item() method would return the associated Item model.