Django在多对多关系上使用count筛选

huangapple go评论63阅读模式
英文:

Django filter on many to many with having count

问题

在很多尝试后,我无法将这种类型的SQL查询转化为Django过滤器。
基本上,它是检索至少具有x个选定标签的所有用户(以及其他过滤器)。
在两个标签的情况下,用户需要同时拥有两个标签,我不希望检索至少有一个标签的用户。

以下是我的定义:

class User(models.Model):
    name = models.CharField(max_length=64)
    tags = models.ManyToManyField(Tag)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
class Tag(models.Model):
    name = models.CharField(max_length=128)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

SQL查询看起来像这样:

SELECT user.id
FROM user
INNER JOIN "user_tags" ON ("user"."id" = "user_tags"."user_id")
AND "user_tags"."tag_id" in (8, 163) group by "user"."id", user_tags.user_id having count(*) = 2
ORDER BY user.id DESC LIMIT 100;

正如您所看到的,having count(*) 的部分是在创建的m2m表 user_tags 上执行的,标签的过滤也是 "user_tags"."tag_id" in (8, 163)

在Django中是否可以实现这个目标?
我所有的尝试都使用了 usertab 表,从未使用过创建的表。

谢谢。

编辑:将 @preeti-y 标记为正确答案,因为看起来这是正确的方法。
但是对于我的特定情况和性能原因,我直接使用了 RawSQL 方法,允许我正确地传递它以及其他过滤器。

Q(id__in=RawSQL(
                    '''SELECT U0.id FROM "user" U0 
                    INNER JOIN "user_tags" U1 ON (U0."id" = U1."user_id") 
                    WHERE U1."tag_id" IN %s
                    GROUP BY U0."id" 
                    HAVING COUNT(U1."user_id") = %s''', (tuple(value), len(value),)))
英文:

After a lot of try, I am not able to translate this kind of sql queries into django filtering.
Basically, it is retrieving all users with at least x selected tags (and other filters).
In the case of two tags, users need to have both tags, I don't want to retrieve users with at least one of the tag.

Here are my definitions:

class User(models.Model):
    name = models.CharField(max_length=64)
    tags = models.ManyToManyField(Tag)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
class Tag(models.Model):
    name = models.CharField(max_length=128)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

SQL query will look like:

SELECT user.id
FROM user
INNER JOIN "user_tags" ON ("user"."id" = "user_tags"."user_id")
AND "user_tags"."tag_id" in (8, 163) group by "user"."id", user_tags.user_id having count(*) = 2
ORDER BY user.id DESC LIMIT 100;

As you can see, the having count(*) part is done on the created m2m table user_tags, and the filtering of tags also "user_tags"."tag_id" in (8, 163)

Is it achievable in a django way?
All my attempts use user and tab tables, never the created table.

Thanks,

EDIT: passing @preeti-y as right answer as it looks like this is the way to go.
But for my specific case and for performances reasons, I directly used a RawSQL method allowing me to correctly pass it with other filters.

Q(id__in=RawSQL(
                    '''SELECT U0.id FROM "user" U0 
                    INNER JOIN "user_tags" U1 ON (U0."id" = U1."user_id") 
                    WHERE U1."tag_id" IN %s
                    GROUP BY U0."id" 
                    HAVING COUNT(U1."user_id") = %s''', (tuple(value), len(value),)))

答案1

得分: 0

根据您的查询,在Django中,您可以使用以下方式在注释中使用filter参数进行Count()操作:

User.objects.annotate(
  matched_tag_count=Count('tags', filter=Q(tags__in=[8, 163]))
).filter(matched_tag_count=2)
英文:

Based on your query, in Django way, you can do Count() with filter argument in the annotation like this:

User.objects.annotate(
  matched_tag_count=Count('tags', filter=Q(tags__in=[8, 163]))
).filter(matched_tag_count=2)

huangapple
  • 本文由 发表于 2023年7月24日 18:20:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76753514.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定