How do I make a list of field values on annotated Django queryset results?

Question

This may be out there somewhere, but not quite sure how to ask it. Hopefully it will be a quick one. Say I have a table like the following:

name         count   site
Guy Man      2       ABC
Guy Man      3       BCD
Guy Man      4       CDE
Girl Woman   2       ABC
Girl Woman   2       BCD
Girl Woman   3       CDE

I would like to annotate these, so that I get a name, total count, and list of sites. So given the data above, I'd get the following.

[
  {
    "name": "Guy Man",
    "count_total": 9,
    "site_list": "ABC, BCD, CDE"
  },
  {
    "name": "Girl Woman",
    "count_total": 7,
    "site_list": "ABC, BCD, CDE"
  }
]

I understand how to get count_total, but I'm not sure how to get site_list. Any ideas?

bdoubleu · Accepted Answer · 2019-08-12 14:56:20Z

5

Solution for PostgreSQL grouping by name field in combination with ArrayAgg and the distinct=True flag:

from django.contrib.postgres.aggregates.general import ArrayAgg
from django.db.models import Count

Model.objects.values('name').annotate(
    count_total=Count('count'),
    site_list=ArrayAgg('site', distinct=True),
)

answered Aug 12, 2019 at 14:56

bdoubleu

6,1473 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hancho · Accepted Answer · 2019-08-12 14:54:26Z

1

If you are using postgres you can use ArrayAgg

docs - https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/aggregates/

You can use values to group by and something like the following should do the trick. Model.objects.values('name').distinct().annotate(site_list=ArrayAgg('site'), count_total=Count('count'))

answered Aug 12, 2019 at 14:54

hancho

1,4573 gold badges24 silver badges44 bronze badges

Collectives™ on Stack Overflow

How do I make a list of field values on annotated Django queryset results?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related