#29555: Left outer join with extra condition
-------------------------------------+-------------------------------------
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.
--
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/056.6e8aa7518e1583c589910770d5c9a42a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
