CODEWITHSUNDEEP
pythondjangopostgresqlfilterarray-agg
jlandercy
jlandercy

Reputation: 11052

Filtering site with specific tags in Django while keeping all site tags aggregated in annotation field

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.

Upvotes: 0

Views: 46

Answers (1)

jlandercy
jlandercy

Reputation: 11052

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.

Upvotes: 0

Related Questions