Opened 2 years ago

Last modified 2 years ago

#25590 new New feature

Allow fields to set join class

Reported by: Anssi Kääriäinen Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (3)

comment:1 Changed 2 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 2 years ago by Adam (Chainz) 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?

comment:3 Changed 2 years ago by 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).

Note: See TracTickets for help on using tickets.
Back to Top