Django queryset-列IN()的GROUP BY拥有不同的计数 [英] Django queryset - column IN() GROUP BY HAVING COUNT DISTINCT

问题描述

使用以下型号:

class Post(models.Model):

    class Meta:
        db_table = "posts"

class Tag(models.Model):
    tag = models.CharField(max_length=50)

   class Meta:
       db_table = "tags"

class PostTag(models.Model):
    postid = models.PositiveIntegerField()
    tagid = models.PositiveIntegerField()

    class Meta:
        unique_together = ("postid", "tagid")
        db_table = "posttags"

要获取包含全部 TAGLIST中给出的tagid的帖子的postid，其中TAGLEN是TAGLIST中的tagid的数量:

To get postids of posts which contain all the tagids given in TAGLIST where TAGLEN is the number of tagids in TAGLIST:

SELECT postid
FROM posttags
WHERE tagid IN (TAGLIST)
GROUP BY postid
HAVING COUNT(DISTINCT tagid) = TAGLEN

但是我该如何使用Django ORM?

But how do I do this with Django ORM?

推荐答案

我找到了解决方案.

    TAGLEN = TAGLIST.count()
    withtags = PostTag.objects.filter(tagid__in=TAGLIST)
    withall = withtags.values("postid").annotate(tagtotal=Count("tagid", distinct=True)).order_by()
    withall.filter(tagtotal=TAGLEN).values_list("postid", flat=True)

在所有这些代码上运行 .query .__ str __()基本上会返回以下SQL.

And running .query.__str__() on all this returns basically the following SQL below.

SELECT "postid"
FROM "posttags"
WHERE "tagid" IN (TAGLIST)
GROUP BY "postid"
HAVING COUNT(DISTINCT "tagid") = TAGLEN'

这篇关于Django queryset-列IN()的GROUP BY拥有不同的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！