Opened 8 years ago

Closed 6 years ago

Last modified 6 years ago

#25590 closed New feature (duplicate)

Allow fields to set join class

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


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 (5)

comment:1 by Tim Graham, 8 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Adam Johnson, 8 years ago

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 by Anssi Kääriäinen, 8 years ago

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

comment:4 by Tyson Clugg, 6 years ago

Cc: Tyson Clugg added

comment:5 by Josh Smeaton, 6 years ago

Resolution: duplicate
Status: newclosed

Closing in favor of #29262.

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

Last edited 6 years ago by Tim Graham (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top