Allow fields to set join class

[Anssi Kääriäinen]

If fields can set the class generating SQL for the join condition, that would allow a way to inject complex raw SQL into ORM queries. The class generating the join condition is currently hardcoded to django.db.models.sql.datastructures.Join.

My use case is to do a join, where for each main object I need to fetch the currently active, or the next active related object. Think of hotel room reservations, where you want to have a list that shows the current occupant of the room. But with a twist where if there is no current occupant, then the next occupant of the room will be shown.

I can do this manually using PostgreSQL with the following query:

select *
 from room left join lateral (
        select *
          from room_reservation
         where room.room_id = room_reservation.room_id and
               from_date = (
                select min(from_date)
                  from room_reservation inner_rr
                 where inner_rr.room_id = room_reservation.room_id
                       and (inner_rr.to_date > now() or inner_rr.to_date is null)
        )
) as current_or_next_room_reservation on true;

Now, 1) I need a complex query inside the join, and 2) I need a lateral join. Both of these are currently out of reach for Django.

If the join field could return a different class to be used instead of the Django's currently hard coded Join class, then 3rd party field implementations could generate exactly the SQL I want.

I believe the changes needed for custom join classes to be actually fairly minor. I don't believe we want to make this public API, so we'd add just a couple of minor changes to internals.

[Adam Johnson]

You want a lateral join on this column for the one query, but that doesn't mean it necessarily applies to all queries containing joins on this column, does it? Wouldn't this change lead to hacks like duplicating your model with managed=False to change that one field so you can use both join types in your application?

[Anssi Kääriäinen]

The idea is to use this for virtualr elation fields. Such fields are already possible (see django-reverse-unique for example).

[Josh Smeaton]

Closing in favor of #29262.

Can undo the close if you think this should remain open.