Allow fields to set join class
|Database layer (models, ORM)
|Patch needs improvement:
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.