Left outer join with extra condition

[Enric Calabuig]

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.

[Simon Charette]

Hello Enric,

Since the introduction of FilteredRelation ​in Django 2.0 you should be able to achieved exactly what you're after with the following

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().

[Tim Graham]

There is also #29262 which talks about custom left outer joins.