[Django] #25590: Allow fields to set join class

Django

unread,

Oct 22, 2015, 3:19:46 AM10/22/15

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/25590>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 22, 2015, 8:08:32 AM10/22/15

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/25590#comment:1>

Django

unread,

Nov 7, 2015, 5:43:56 AM11/7/15

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by adamchainz):

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?

--
Ticket URL: <https://code.djangoproject.com/ticket/25590#comment:2>

Django

unread,

Nov 7, 2015, 5:51:05 AM11/7/15

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by akaariai):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/25590#comment:3>

Django

unread,

Mar 8, 2018, 5:27:26 AM3/8/18

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------

Reporter: Anssi Kääriäinen | Owner: nobody

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tyson Clugg):

* cc: Tyson Clugg (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/25590#comment:4>

Django

unread,

Mar 28, 2018, 3:58:43 AM3/28/18

to django-...@googlegroups.com

#25590: Allow fields to set join class
-------------------------------------+-------------------------------------

Reporter: Anssi Kääriäinen | Owner: nobody

Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------