django annotate with dynamic column name

Question

I have a model in django app, with the following structure:

class items(models.Model):
    name = models.CharField(max_length=50)
    location = models.CharField(max_length=3)

I wanted to create a pivot table for the count of each location per each name/item, which I managed to do as per the following:

queryset_res = items.objects.values('name')\
                            .annotate(NYC=Sum(Case(When(location='NYC', then=1),default=Value('0'),output_field=IntegerField())))\
                            .annotate(LND=Sum(Case(When(location='LND', then=1),default=Value('0'),output_field=IntegerField())))\
                            .annotate(ASM=Sum(Case(When(location='ASM', then=1),default=Value('0'),output_field=IntegerField())))\
                            .annotate(Total=Count('location'))\
                            .values('name', 'NYC', 'LSA','Total')\
                            .order_by('-Total')

This gives me how many times each name appears against each location which is all ok.

my question is how can I make the location dynamic, and so if new locations where added I don't have come back and change the code again! either from a list or from the model data itself

Many Thanks AB

Species

Also check out the django-pivot github.com/martsberger/django-pivot library. Does it already do exactly what you want? – Sabba

Answer 1

You can bind dynamic parameter with *[1, 2, 3], **{'key': 'value'} in python.

from django.db.models import Case, Count, Sum, IntegerField, Value, When

def get_annotation(key):
    return {
        key: Sum(
            Case(
                When(location=key, then=Value(1)),
                default=Value(0),
                output_field=IntegerField(),
           ),
        ),
    }

queryset_res = items.objects.values('name')
location_list = ['NYC', 'LSA', 'ASM', ...etc]
for key in location_list:
    queryset_res = queryset_res.annotate(**get_annotation(key))
    
queryset_res = (
    queryset_res.annotate(Total=Count("location"))
    .values("name", "Total", *location_list)
    .order_by("-Total")
)

Now you can implement a set of queries simply by changing location_list.

Geter

hi, thank you for the response, it works perfectly, just only little correction as you have missed the return key_map in get_annotation, other than that, perfection, thank you – Species

Ah, I forgot that. Sorry. @Species – Geter