在Django中使用特定标签筛选网站,同时将所有网站标签聚合在注释字段中。

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

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-1type-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.

huangapple
  • 本文由 发表于 2023年2月14日 21:01:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75448230.html
匿名

发表评论

匿名网友

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

确定