Render new table with values from a differently modeled queryset

Question

I'm using django-tables2 to display data stored in an SQLite database like this:

(Table1:) 
id  field1  field2  field3
1   George  green   42
2   George  blue    72
3   George  yellow  1
4   James   brown   10

Now, I'd like to reorganize this table and use values from field2 as new columns and fill the cells with values from field3:

(Table 2:)
field1  green  blue  yellow  brown
George  42     72    1
James                        10

I have django models and tables for both tables and I could theoretically hard code the model for table 2 (although it would be more elegant to do this dynamically, but first things first).

My question is now: What's the best way to perform this transformation?

Basic idea:

queryset = Model1.objects.all()
# reorganize queryset somehow
table = Table2(queryset)

I have the feeling that this could be done in a better way. I'd be happy about any suggestions!

Thanks, Philip

Answer 1

You can try this:

from django.db.models import Sum

all_items = Model1.objects.all()
names = Model1.objects.values_list('field1', flat=True)
name_set = set(names)

items = []

color_choices = ['green', 'blue', 'yellow']

for name in name_set:
    name_info = {
        'field1': name,
    }

    for color_choice in color_choices:
        result_sum = all_items.filter(field1=name, field2=color_choice).aggregate(color_sum=Sum('field3'))
        name_info[color_choice] = result_sum['color_sum']

    items.append(name_info)

table = Table2(items)

This code gets the unique names using a set, then for each name, it computes the gray, blue and yellow properties (which is calculated as the sum of all of the quantities for the people, using the specified unique name who selected that color). All of this information is collected in a list of dictionaries in the variable items, which is then passed to django-tables2 for rendering purposes.

Then just configure django-tables2 to display the new data accordingly.