如何在Django中实现Where Exists？

Question

我在Django中有两个模型，一个是歌曲(Songs)，另一个是专辑(Albums)，一个专辑可以包含多首歌曲。我想要筛选出包含有效歌曲的专辑。例如，至少有一首歌曲需要有音频文件才能通过筛选返回该专辑。我使用的是Postgres数据库。

我正在尝试通过Django QuerySet实现这种逻辑，但不确定如何使用where exists而不是exists。

以下是我正在尝试使其工作的Django ORM语句：

valid_songs = Song.objects.filter(
    album=OuterRef('pk'),
    audio_file__isnull=False).only("album")

Album.objects.annotate(
    valid_song=Exists(valid_songs)).filter(
valid_song=True).query

最初的回答：

这是生成的查询：

SELECT "api_album"."id", 
       "api_album"."created_at", 
       "api_album"."updated_at", 
       "api_album"."title", 
       "api_album"."artwork_file_id", 
       "api_album"."user_id", 
       "api_album"."description", 
       "api_album"."tags", 
       "api_album"."genres", 
       EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) AS "valid_song" 
FROM   "api_album" 
WHERE  EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) = true 

这是由Django的QuerySet生成的上述查询的Postgres查询计划：
原始答案翻译成“最初的回答”。

Seq Scan on api_album  (cost=0.00..287.95 rows=60 width=641)
 Filter: (alternatives: SubPlan 3 or hashed SubPlan 4)
 SubPlan 3
   ->  Seq Scan on api_song u0_2  (cost=0.00..1.54 rows=1 width=0)
         Filter: ((audio_file_id IS NOT NULL) AND (album_id = api_album.id))
 SubPlan 4
   ->  Seq Scan on api_song u0_3  (cost=0.00..1.43 rows=10 width=4)
         Filter: (audio_file_id IS NOT NULL)
 SubPlan 1
   ->  Seq Scan on api_song u0  (cost=0.00..1.54 rows=1 width=0)
         Filter: ((audio_file_id IS NOT NULL) AND (album_id = api_album.id))
 SubPlan 2
   ->  Seq Scan on api_song u0_1  (cost=0.00..1.43 rows=10 width=4)
         Filter: (audio_file_id IS NOT NULL)
(14 rows)

然而，有一种更有效的查询方法。最初的回答。

SELECT * 
FROM   "api_album" 
WHERE  EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) 

Hash Semi Join  (cost=1.55..13.26 rows=10 width=640)
 Hash Cond: (api_album.id = u0.album_id)
 ->  Seq Scan on api_album  (cost=0.00..11.20 rows=120 width=640)
 ->  Hash  (cost=1.43..1.43 rows=10 width=4)
       ->  Seq Scan on api_song u0  (cost=0.00..1.43 rows=10 width=4)
             Filter: (audio_file_id IS NOT NULL)
(6 rows)

因此，我的问题如下：

1. 在这种情况下，where exists与exists之间有什么区别，并且为什么不会创建相同的查询计划？
2. 如何使Django ORM生成更有效的查询？

编辑：django模型如下：

(请注意，本文并没有提供原始答案，因此“Original Answer”无法被正确翻译。)

  class Album(BaseModel):
    title = models.CharField(max_length=255, blank=False)
    artwork_file = models.ForeignKey(
        S3File, null=True, on_delete=models.CASCADE,
        related_name="album_artwork_file")
    user = models.ForeignKey(settings.AUTH_USER_MODEL,
                             related_name="albums",
                             on_delete=models.CASCADE)
    description = models.TextField(blank=True)
    tags = ArrayField(models.CharField(
        max_length=16), default=default_arr)
    genres = ArrayField(models.CharField(
        max_length=16), default=default_arr)



class Song(BaseModel):
    title = models.CharField(max_length=255, blank=False)
    album = models.ForeignKey(Album,
                              related_name="songs",
                              on_delete=models.CASCADE)
    audio_file = models.ForeignKey(
        S3File, null=True, on_delete=models.CASCADE,
        related_name="song_audio_file")

以下代码无法运行，因为如果你在这个查询集上使用get()方法，会抛出异常

润色后的内容：

下面的代码不可用，因为在这个查询集上使用get()方法会抛出异常。

Album.objects.filter(songs__audio_file__isnull=False).get(pk=1)
Album.MultipleObjectsReturned: get() returned more than one Album 

在DjangoRest ModelViewSet中使用查询集进行crud操作，并将其传递给Album Serializer。这需要get()函数能够正常工作并返回单个值。

翻译结果：使用DjangoRest的ModelViewSet时，会用到查询集来进行增删改查操作，并将其传递给Album Serializer。为此，必须让get()函数正常工作并返回单个值。

class AlbumViewSet(viewsets.ModelViewSet):

    serializer_class = AlbumSerializer

    def get_queryset(self): 

        valid_songs = Song.objects.filter(
            album=OuterRef('pk'),
            audio_file__isnull=False).only('album')

        # Slow query posted above
        return Album.objects.annotate(
            valid_song=Exists(valid_songs)
        ).filter(valid_song=True)

- user1152226

Answer 1

最初的回答：我不确定你为什么要执行这两个查询。查找至少有一首歌曲带有音频文件的专辑可以简单地表示为：

翻译结果：

我不确定你为什么会想要执行这两个查询。如果想要找到至少包含一首带有音频文件的歌曲的专辑，可以简单地使用以下语句：

Album.objects.filter(song__audio_file__isnull=False)

- Daniel Roseman

---

这不起作用，因为专辑的外键在歌曲上。 - user1152226

歌曲不是专辑上的一个字段，这将引发一个异常。django.core.exceptions.FieldError: 无法将关键字'song'解析为字段。 - user1152226

看了你的修改，我觉得你是想建议使用Album.objects.filter(songs__audio_file__isnull=False)，但是当与get()一起使用时会导致异常。 - user1152226

啊，你已经设置了related_name，所以你需要使用它：songs__audio_file__isnull。 - Daniel Roseman

是的，那么为什么你要使用get呢？你想要多个专辑。应该使用filter。 - Daniel Roseman

Album.objects.filter(songs__audio_file__isnull=False) 会导致内连接，如果一个专辑有多首歌曲，则会返回重复的专辑。这不是我想要的结果。 - user1152226