[Django] #29555: Left outer join with extra condition
Django
-------------------------------------+-------------------------------------
Reporter: Enric | Owner: nobody
Calabuig |
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords: Queryset.extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I posted the question below on StackOverflow and the best approach
possible to solving it as of now seems to be using `.extra()`. My feeling
is that this is a rather common scenario so it could be that there is a
better way of getting what I want already.
Link to the question: [https://stackoverflow.com/questions/51175110/how-
to-left-outer-join-with-extra-condition-in-django]
----
I have these three models:
{{{
class Track(models.Model):
title = models.TextField()
artist = models.TextField()
class Tag(models.Model):
name = models.CharField(max_length=50)
class TrackHasTag(models.Model):
track = models.ForeignKey('Track', on_delete=models.CASCADE)
tag = models.ForeignKey('Tag', on_delete=models.PROTECT)
}}}
And I want to retrieve all Tracks that are not tagged with a specific tag.
This gets me what I want:
`Track.objects.exclude(trackhastag__tag_id='1').only('id')` but it's very
slow when the tables grow. This is what I get when printing `.query` of
the queryset:
{{{
SELECT "track"."id"
FROM "track"
WHERE NOT ( "track"."id" IN (SELECT U1."track_id" AS Col1
FROM "trackhastag" U1
WHERE U1."tag_id" = 1) )
}}}
I would like Django to send this query instead:
{{{
SELECT "track"."id"
FROM "track"
LEFT OUTER JOIN "trackhastag"
ON "track"."id" = "trackhastag"."track_id"
AND "trackhastag"."tag_id" = 1
WHERE "trackhastag"."id" IS NULL;
}}}
But haven't found a way to do so. Using a Raw Query is not really an
option as I have to filter the resulting queryset very often.
The cleanest workaround I have found is to create a view in the database
and a model TrackHasTagFoo with managed = False that I use to query like:
`Track.objects.filter(trackhastagfoo__isnull=True)`. I don't think this is
an elegant nor sustainable solution as it involves adding Raw SQL to my
migrations to mantain said view.
This is just one example of a situation where we need to do this kind of
left join with an extra condition, but the truth is that we are facing
this problem in more parts of our application.
--
Ticket URL: <https://code.djangoproject.com/ticket/29555>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Queryset.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Hello Enric,
Since the introduction of `FilteredRelation`
[https://docs.djangoproject.com/en/2.0/ref/models/querysets
/#filteredrelation-objects in Django 2.0] you should be able to achieved
exactly what you're after with the following
{{{#!python
Track.objects.annotate(
has_tag=FilteredRelation('trackhastag',
condition=Q(trackhastag__tag=1)),
).filter(
has_tag__isnull=True,
)
}}}
I'll mark this ticket as ''invalid'' for now but feel free to re-open it
if it doesn't match your use case of `extra()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/29555#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: Queryset.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
--
Ticket URL: <https://code.djangoproject.com/ticket/29555#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: Queryset.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
There is also #29262 which talks about custom left outer joins.
--
Ticket URL: <https://code.djangoproject.com/ticket/29555#comment:3>