I have two models. One is Branch with branch_id and branch_name fields and Another is Sales with sales_amount and year field. Branch and Sales have a ForeignKey relationship. A Branch can have multiple Sales. What I'm trying to achieve is to group by with year where each year has its sales value. And get the dictionary of key(Branch name) and values(sales_list) If there are no sales for that year then there should be value '0'.
models.py
import datetime
from django.db import models
class Branch(models.Model):
branch_id = models.IntegerField()
name = models.CharField(max_length=50)
def __str__(self):
return self.name
class Sales(models.Model):
branch = models.ForeignKey('Branch', on_delete = models.CASCADE)
sales_amount = models.IntegerField()
year = models.CharField(max_length=4)
class Meta:
unique_together = (('year', 'branch'),)
def __str__(self):
return self.branch.name
views.py
from django.shortcuts import render
import json
from .models import Branch, Sales
def index(request):
branches = Branch.objects.all()
sales = Sales.objects.all()
sales_list = []
branch_list = []
year_set = set()
for sale in sales:
year_set.add(sale.year)
year_list = list(sorted(year_set))
for branch in branches:
sales_list.append(branch.sales_set.all().order_by('year'))
branch_list.append(branch.name)
sales_list = [[j.sales_amount for j in i] for i in sales_list]
branch_dict = dict(zip(branch_list, sales_list))
return render(request, 'index.html', {
'year_list': year_list,
'branch_dict': branch_dict
})
I've
BRANCH001 with the year 2011 has 5000 sales, BRANCH001 with the year 2012 has 10000 sales
,
BRANCH002 with the year 2011 has 7000 sales
expected output = {'BRANCH001': [5000, 10000], 'BRANCH002': [0, 7000]}
actual output = {'BRANCH001': [5000, 10000], 'BRANCH002': [7000]}