#27332: Specifying additional JOIN arguments
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version:
(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
-------------------------------------+-------------------------------------
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.855fc9de49b348e0d41be1a7a3d320d2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
