How to left outer join with extra condition in Django

[Enric Calabuig]

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.

Thanks a lot!


P.D: I have also posted this in stackoverflow here
<https://stackoverflow.com/q/51175110/8069075>.

[Benjamin Toueg]

Hi,

I've answered on stackoverflow. Here is an excerpt:

from django.db.models import Q
Track.objects.filter(
# work around to force left outer join
Q(trackhastag__isnull=True) | Q(trackhastag__isnull=False)).extra(
# where parameters are “AND”ed to any other search criteria
# thus we need to account for NULL
where=[
'"app_trackhastag"."id" <> %s or "app_trackhastag"."id" is NULL'
],
params=[1],)

produces this somewhat convoluted query:

SELECT "app_track"."id", "app_track"."title", "app_track"."artist"
FROM "app_track"
LEFT OUTER JOIN "app_trackhastag"
ON ("app_track"."id" = "app_trackhastag"."track_id")
WHERE (
("app_trackhastag"."id" IS NULL OR "app_trackhastag"."id" IS NOT NULL) AND
("app_trackhastag"."id" <> 1 or "app_trackhastag"."id" is NULL))

T
​his does not break the ORM, queryset can be further filtered.​

Post by Enric Calabuig
title = models.TextField()
artist = models.TextField()
name = models.CharField(max_length=50)
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.
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
SELECT "track"."id"
FROM "track"
WHERE NOT ( "track"."id" IN (SELECT U1."track_id" AS Col1
FROM "trackhastag" U1
WHERE U1."tag_id" = 1) )
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
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.
Thanks a lot!
P.D: I have also posted this in stackoverflow here
<https://stackoverflow.com/q/51175110/8069075>.
--
You received this message because you are subscribed to the Google Groups
"Django REST framework" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.