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.

[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.​

--
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 email to django-rest-fram...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.