Opened 14 months ago

Closed 14 months ago

Last modified 14 months ago

#29555 closed Uncategorized (invalid)

Left outer join with extra condition

Reported by: Enric Calabuig Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: Queryset.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (3)

comment:1 Changed 14 months ago by 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().

comment:2 Changed 14 months ago by Simon Charette

Resolution: invalid
Status: newclosed

comment:3 Changed 14 months ago by Tim Graham

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

Note: See TracTickets for help on using tickets.
Back to Top