[Django] #27332: Specifying additional JOIN arguments
Django
----------------------------------------------+--------------------
Reporter: MikiSoft | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version:
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
For example, this raw SQL:
{{{
SELECT event.*
FROM event
INNER JOIN
business
ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
'like'
ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;
}}}
Can't be translated to equivalent Django ORM expression even if using
`extra()` command, because there isn't anywhere a possibility to set
additional arguments on `JOIN` clauses. And when I'm using
`filter(Q(argument) | ...)` it happens to always push all arguments to
`WHERE` clause, which just causes a performance hit. I wish there was some
parameter which would determine the destination of the argument, whether
it has to go to the (last) `JOIN` clause, or to be put in `WHERE` (which
goes by default). Also, I'm not able to comprehend how to perform
correctly `LEFT JOIN` so in the end I have made some jumbled up expression
which is completely inefficient (as it executes two queries, but it's the
only way to do the same from above and get `QuerySet` in return):
{{{
from django.db.models import Q, Coalesce
Event.objects.filter(Q(pk__in=Like.objects.filter(person__pk=1,
content_type=17).prefetch_related('content_object').values_list('object_id',
flat=True)) | Q(business__manager=1)).order_by(Coalesce('likes__date',
'when').desc())
}}}
I'm filing this issue because some (or many?) people like me desperately
need output in a form of a `QuerySet` because of the pagination and many
other things `RawQuerySet` doesn't support, so using `raw()` isn't
definitely an option in my case. Django has left me out of choice.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: MikiSoft | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by MikiSoft):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
New description:
For example, this raw SQL:
{{{
SELECT event.*
FROM event
INNER JOIN
business
ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
'like'
ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;
}}}
Can't be translated to the **equivalent** Django ORM expression even if
using `extra()` command, because there isn't anywhere a possibility to set
additional arguments on `JOIN` clauses. And when I'm using
`filter(Q(argument) | ...)` it happens to always push all arguments to
`WHERE` clause, which just causes a performance hit. I wish there was some
parameter which would determine the destination of the argument, whether
it has to go to the (last) `JOIN` clause, or to be put in `WHERE` (which
goes by default). Also, I'm not able to comprehend how to perform
correctly `LEFT JOIN` so in the end I have made some jumbled up expression
which is completely inefficient (as it executes two queries, but it's the
only way to do the same from above and get `QuerySet` in return):
{{{
from django.db.models import Q
from django.db.models.functions import Coalesce
Event.objects.filter(Q(pk__in=Like.objects.filter(person__pk=1,
content_type__pk=17).prefetch_related('content_object').values_list('object_id',
flat=True)) | Q(business__manager=1)).order_by(Coalesce('likes__date',
'when').desc())
}}}
I'm filing this issue because some (or probably many) people like me
desperately need output in a form of a `QuerySet` because of the
pagination and many other things `RawQuerySet` doesn't support, so using
`raw()` isn't definitely an option in my case. Django has left me out of
choice.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:1>
Django
Reporter: MikiSoft | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by MikiSoft:
Old description:
> For example, this raw SQL:
> {{{
> SELECT event.*
> FROM event
> INNER JOIN
> business
> ON (event.business_id = business.id AND business.manager_id = 1)
> LEFT JOIN
> 'like'
> ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
> ORDER BY COALESCE('like'.date, event.'when') DESC;
> }}}
>
> Can't be translated to the **equivalent** Django ORM expression even if
> using `extra()` command, because there isn't anywhere a possibility to
> set additional arguments on `JOIN` clauses. And when I'm using
> `filter(Q(argument) | ...)` it happens to always push all arguments to
> `WHERE` clause, which just causes a performance hit. I wish there was
> some parameter which would determine the destination of the argument,
> whether it has to go to the (last) `JOIN` clause, or to be put in `WHERE`
> (which goes by default). Also, I'm not able to comprehend how to perform
> correctly `LEFT JOIN` so in the end I have made some jumbled up
> expression which is completely inefficient (as it executes two queries,
> but it's the only way to do the same from above and get `QuerySet` in
> return):
>
> {{{
> from django.db.models import Q
> from django.db.models.functions import Coalesce
>
> Event.objects.filter(Q(pk__in=Like.objects.filter(person__pk=1,
> content_type__pk=17).prefetch_related('content_object').values_list('object_id',
> flat=True)) | Q(business__manager=1)).order_by(Coalesce('likes__date',
> 'when').desc())
> }}}
>
> I'm filing this issue because some (or probably many) people like me
> desperately need output in a form of a `QuerySet` because of the
> pagination and many other things `RawQuerySet` doesn't support, so using
> `raw()` isn't definitely an option in my case. Django has left me out of
> choice.
New description:
For example, this raw SQL:
{{{
SELECT event.*
FROM event
INNER JOIN
business
ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
'like'
ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;
}}}
Can't be translated to the **equivalent** Django ORM expression even if
using `extra()` command, because there isn't anywhere a possibility to set
additional arguments on `JOIN` clauses. And when I'm using
`filter(Q(argument) | ...)` it happens to always push all arguments to
`WHERE` clause, which just causes a performance hit. I wish there was some
parameter which would determine the destination of the argument, whether
it has to go to the (last) `JOIN` clause, or to be put in `WHERE` (which
goes by default). Also, I'm not able to comprehend how to perform
correctly `LEFT JOIN` so in the end I have made some jumbled up expression
which is completely inefficient (as it executes two queries, but it's the
only way to do the same from above and get `QuerySet` in return):
{{{
from django.db.models import Q
from django.db.models.functions import Coalesce
Event.objects.filter(Q(pk__in=Like.objects.filter(person_id=1,
content_type_id=17).prefetch_related('content_object').values_list('object_id',
flat=True)) | Q(business__manager_id=1)).order_by(Coalesce('likes__date',
'when').desc())
}}}
I'm filing this issue because some (or probably many) people like me
desperately need output in a form of a `QuerySet` because of the
pagination and many other things `RawQuerySet` doesn't support, so using
`raw()` isn't definitely an option in my case. Django has left me out of
choice.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:2>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by MikiSoft):
* type: Uncategorized => New feature
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:3>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by MikiSoft:
Old description:
> For example, this raw SQL:
> {{{
> SELECT event.*
> FROM event
> INNER JOIN
> business
> ON (event.business_id = business.id AND business.manager_id = 1)
> LEFT JOIN
> 'like'
> ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
> ORDER BY COALESCE('like'.date, event.'when') DESC;
> }}}
>
> Can't be translated to the **equivalent** Django ORM expression even if
> using `extra()` command, because there isn't anywhere a possibility to
> set additional arguments on `JOIN` clauses. And when I'm using
> `filter(Q(argument) | ...)` it happens to always push all arguments to
> `WHERE` clause, which just causes a performance hit. I wish there was
> some parameter which would determine the destination of the argument,
> whether it has to go to the (last) `JOIN` clause, or to be put in `WHERE`
> (which goes by default). Also, I'm not able to comprehend how to perform
> correctly `LEFT JOIN` so in the end I have made some jumbled up
> expression which is completely inefficient (as it executes two queries,
> but it's the only way to do the same from above and get `QuerySet` in
> return):
>
> {{{
> from django.db.models import Q
> from django.db.models.functions import Coalesce
>
> Event.objects.filter(Q(pk__in=Like.objects.filter(person_id=1,
> content_type_id=17).prefetch_related('content_object').values_list('object_id',
> flat=True)) | Q(business__manager_id=1)).order_by(Coalesce('likes__date',
> 'when').desc())
> }}}
>
> I'm filing this issue because some (or probably many) people like me
> desperately need output in a form of a `QuerySet` because of the
> pagination and many other things `RawQuerySet` doesn't support, so using
> `raw()` isn't definitely an option in my case. Django has left me out of
> choice.
New description:
For example, this raw SQL:
{{{
#!sql
SELECT event.*
FROM event
INNER JOIN
business
ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
'like'
ON (event.id = object_id AND content_type_pk = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;
}}}
Can't be translated to the **equivalent** Django ORM expression even if
using `extra()` command, because there isn't anywhere a possibility to set
additional arguments on `JOIN` clauses. And when I'm using
`filter(Q(argument) | ...)` it happens to always push all arguments to
`WHERE` clause, which just causes a performance hit. I wish there was some
parameter which would determine the destination of the argument, whether
it has to go to the (last) `JOIN` clause, or to be put in `WHERE` (which
goes by default). Also, I'm not able to comprehend how to perform
correctly `LEFT JOIN` so in the end I have made some jumbled up expression
which is completely inefficient (as it executes two queries, but it's the
only way to do the same from above and get `QuerySet` in return):
{{{
#!python
Event.objects.filter(Q(pk__in=Like.objects.filter(person_id=1,
content_type_id=17).prefetch_related('content_object').values_list('object_id',
flat=True)) | Q(business__manager_id=1)).order_by(Coalesce('likes__date',
'when').desc())
}}}
I'm filing this issue because some (or probably many) people like me
desperately need output in a form of a `QuerySet` because of the
pagination and many other things `RawQuerySet` doesn't support, so using
`raw()` isn't definitely an option in my case. Django has left me out of
choice.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:4>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #26426.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:5>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: duplicate
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by MikiSoft):
[comment:5 Tim Graham],
I understand that it's a duplicate, but in that issue it is provided the
equivalent solution to the problem using `extra()` and `aggregate()`
method, while here there isn't any possible one.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:6>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by MikiSoft):
* status: closed => new
* resolution: duplicate =>
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:8>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
Comment:
I don't know. I'll tentatively accept the ticket but it might not be
feasible to construct every query through the ORM.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:9>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nicolas Delaby):
Hi,
I started to look at this one and realized {{{GenericForeignKey}}}
implemented such functionnality in {{{ get_extra_restriction() }}}
https://github.com/django/django/blob/master/django/contrib/contenttypes/fields.py#L437
So I followed that path, and came up with a PoC implementation (with
passing tests), following this API:
{{{#!python
class ConditionalJoinTests(TestCase):
@classmethod
def setUpTestData(cls):
cls.author1 = Author.objects.create(name='Alice')
cls.author2 = Author.objects.create(name='Jane')
cls.book1 = Book.objects.create(title='Poem by Alice',
editor='A',
author=cls.author1)
cls.book2 = Book.objects.create(title='The book by Jane A',
editor='A',
author=cls.author2)
cls.book2 = Book.objects.create(title='The book by Jane B',
editor='B',
author=cls.author2)
def test_conditional_join_query_wo_join(self):
"""
All Authors are returned because no join is required by the
filters.
"""
self.assertQuerysetEqual(
Author.objects
.conditional_join('book', title__iexact='poem by alice'),
["<Author: Alice>", "<Author: Jane>"])
def test_conditional_join_query_with_join(self):
self.assertQuerysetEqual(
Author.objects
.conditional_join('book', title__iexact='poem by alice')
.filter(book__isnull=False),
["<Author: Alice>"])
self.assertQuerysetEqual(
Author.objects
.conditional_join('book', Q(title__iexact='poem by alice'))
.filter(book__isnull=False),
["<Author: Alice>"])
def test_conditional_join_query_with_join_multiple(self):
self.assertQuerysetEqual(
Author.objects
.conditional_join('book', title__icontains='jane', editor='B')
.filter(book__isnull=False),
["<Author: Jane>"])
}}}
If this proposal receives some interest, I'll be glad to work on preparing
a pull request to support this use case.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:10>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by MikiSoft):
Awesome! Looking forward to its implementation in Django. :)
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:11>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Anssi Kääriäinen):
This is something I've wanted for a long time.
Some considerations:
- In general the ORM API tries to avoid SQL specific terms like join.
Maybe relation would be a better term to use?
- The API shouldn't edit the current relation, instead it should add a
new lookup path alias. So: .filtered_relation('translations',
alias='translation_fi',
condition=Q(translations__lang='fi')).filter(translation_fi__title=...)
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:12>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* cc: Simon Charette (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:13>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nicolas Delaby):
Thanks Anssi for your feedback. I'll start from your proposal.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:14>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Daniel Hahler):
* cc: django@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:15>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Nicolas Delaby):
https://github.com/django/django/pull/7560
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:16>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Nicolas Delaby):
* cc: ticosax@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:17>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Claude Paroz):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:18>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:19>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Daniel Hahler):
PR review comments have been addressed.
Please consider including it for Django 1.11.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:20>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Josh Smeaton):
* cc: josh.smeaton@… (added)
Comment:
Sorry, I wasn't aware this patch existed, or I would have tried to review
for 1.11. I'll make some time to review this patch in the next few weeks.
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:21>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 1 => 0
* version: => master
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:22>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"01d440fa1e6b5c62acfa8b3fde43dfa1505f93c6" 01d440fa]:
{{{
#!CommitTicketReference repository=""
revision="01d440fa1e6b5c62acfa8b3fde43dfa1505f93c6"
Fixed #27332 -- Added FilteredRelation API for conditional join (ON
clause) support.
Thanks Anssi Kääriäinen for contributing to the patch.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27332#comment:23>