如何在Django中保留额外条件的外部联接 [英] How to left outer join with extra condition in Django

问题描述

我有这三个模型：

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)

我想检索所有未使用特定标签标记的曲目。这让我得到了我想要的东西： Track.objects.exclude（trackhastag__tag_id ='1'）。only（'id'），但是当表增长时，它的运行速度非常慢。这是在打印查询集的 .query 时得到的：

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

我希望Django发送此查询而是：

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.

我发现的最干净的解决方法是在数据库中创建一个视图，然后我用来查询的模型 TrackHasTagFoo 具有 managed = False 的模型，例如： Track.objects。 filter（trackhastagfoo__isnull = True）。我不认为这是一个优雅且不可持续的解决方案，因为它涉及在我的迁移中添加Raw SQL来维持上述观点。

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.

非常感谢！

推荐答案

如 Django＃29555 ，您可以为此目的使用 FilteredRelation 自Django 2.0起。

As mentioned in Django #29555 you can use FilteredRelation for this purpose since Django 2.0.

Track.objects.annotate(
    has_tag=FilteredRelation(
        'trackhastag', condition=Q(trackhastag__tag=1)
    ),
).filter(
    has_tag__isnull=True,
)

这篇关于如何在Django中保留额外条件的外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！