通过在Django orm中使用多对多关系来执行组?

2024-03-29 08:42:54 发布

您现在位置:Python中文网/ 问答频道 /正文

我有以下型号:

class Tag(models.Model):
    name = models.CharField(max_length=255, unique=True, default="")


class Problem(models.Model):
    tags = models.ManyToManyField(Tag, related_name="problems")
    index = models.CharField(max_length=5)
    name = models.CharField(max_length=255)
    rating = models.IntegerField(default=-1)

我要执行以下查询:

SELECT
    "p"."index",
    "p"."name",
    "p"."rating"
FROM
    problem p
WHERE
    p.id IN (
        SELECT
            pt.problem_id
        FROM
            problem_tags pt
            JOIN tag t ON pt.tag_id = t.id
        WHERE
            t.name IN ('math', 'binary search', 'implementation')
        GROUP BY
            pt.problem_id
        HAVING
            COUNT(*) = 3
    )
ORDER BY
    rating,
    index;

我使用了类似于:

Problem.tags.through.objects.values("problem_id").filter(
    tag__name__in=("math", "binary search", "implementation")
).annotate(count=models.Count("*")).filter(count=3)

但它发出以下查询,其中SELECT中的第一个COUNT(*)是冗余的和错误的:

SELECT
    "api_problem_tags"."problem_id",
    COUNT(*) AS "count"
FROM
    "api_problem_tags"
    INNER JOIN "api_tag" ON ("api_problem_tags"."tag_id" = "api_tag"."id")
WHERE
    "api_tag"."name" IN ('math', 'binary search', 'implementation')
GROUP BY
    "api_problem_tags"."problem_id"
HAVING
    COUNT(*) = 3

我怎样才能摆脱第一个COUNT(*)


1条回答
网友
1楼 · 发布于 2024-03-29 08:42:54

我想您可以使用Countfilter参数

Problem.objects.annotate(
   tag_count=Count(
       'tags',
       filter=Q(tags__name__in=["math", "binary search", "implementation"],
   )
).filter(tag_count=3)

相关问题 更多 >