#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 [email protected].
To post to this group, send email to [email protected].
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.
