英文:
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中是否可以实现这个目标?
我所有的尝试都使用了 user
和 tab
表,从未使用过创建的表。
谢谢。
编辑:将 @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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论