英文:
Filtering site with specific tags in Django while keeping all site tags aggregated in annotation field
问题
我有以下的Django模型:
class Tag(models.Model):
key = models.CharField(max_length=64, unique=True)
class Site(models.Model):
key = models.CharField(max_length=64, unique=True)
tags = models.ManyToManyField(Tag, through='SiteTag')
class SiteTag(models.Model):
site = models.ForeignKey(Site, on_delete=models.RESTRICT)
tag = models.ForeignKey(Tag, on_delete=models.RESTRICT)
其中,一个站点可以使用显式的多对多关系拥有多个标签。当我使用以下查询筛选特定标签的站点时,其他标签将被筛选掉,我的意思是给我具有这些标签的站点,并显示这些站点具有的所有标签:
Site.objects.filter(tags__key__in=['type-1', 'type-2'])\
.annotate(tags=ArrayAgg("tags__key", distinct=True, filter=Q(tags__isnull=False)))
然后,它确实检索到具有type-1
或type-2
的站点,但如果所选站点具有type-3
标签,我也想在ArrayAgg
聚合中收集它。不幸的是,使用这个查询,我将在注释的标签字段中只有["type-1", "type-2"]
,而不是["type-1", "type-2", "type-3"]
。
如何使用这个条件筛选站点并仍然在注释部分中收集所有标签。
英文:
Let's say I have the following django model:
class Tag(models.Model):
key = models.CharField(max_length=64, unique=True)
class Site(models.Model):
key = models.CharField(max_length=64, unique=True)
tags = models.ManyToManyField(Tag, through='SiteTag')
class SiteTag(models.Model):
site = models.ForeignKey(Site, on_delete=models.RESTRICT)
tag = models.ForeignKey(Tag, on_delete=models.RESTRICT)
Where a site can have multiple tags using explicit Many-to-Many relationship. When I filter site with a specific tag in the following query other tags are filtered out where I mean give me sites with this tags and show all tags those sites have:
Site.objects.filter(tags__key__in=['type-1', 'type-2'])\
.annotate(tags=ArrayAgg("tags__key", distinct=True, filter=Q(tags__isnull=False)))
Then it indeed retrieve sites with either type-1
or type-2
but if a selected site has the type-3
tag I also want to collect it in the ArrayAgg
aggregation. Unfortunetly with this query I will only have ["type-1", "type-2"]
in annotated tags field instead of ["type-1", "type-2", "type-3"]
.
How can I filter sites using this criterion and still have all tags collected in the annotate section.
答案1
得分: 0
有时仅仅编写问题就足以找到解决方案,关键是在annotate
之后和filter
之前调用all
。
Site.objects.annotate(
tags=ArrayAgg("tags__key", distinct=True, filter=Q(tags__isnull=False))
).all().filter(tags__key__in=['type-1', 'type-2'])
此查询按预期进行筛选并返回所有聚合的标签。
英文:
Sometimes writing the question is enough to find out the solution, the key was to call all
after annotate
and before filter
.
Site.objects.annotate(
tags=ArrayAgg("tags__key", distinct=True, filter=Q(tags__isnull=False)
).all().filter(tags__key__in=['type-1', 'type-2'])
This query filters as expected and returns all aggregated tags.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论