id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29555	Left outer join with extra condition	Enric Calabuig	nobody	"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."	Uncategorized	closed	Database layer (models, ORM)	1.11	Normal	invalid	Queryset.extra		Unreviewed	0	0	0	0	0	0