id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25590	Allow fields to set join class	Anssi Kääriäinen	nobody	"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."	New feature	closed	Database layer (models, ORM)	dev	Normal	duplicate		Tyson Clugg	Accepted	0	0	0	0	0	0