Django: Group and then Calculate the Sum of the column values through query-CodePudding

I have a model:

lifecycle_status_choice = (
    ('Lead', 'Lead'),
    ('Opportunity', 'Opportunity'),
    ('Customer', 'Customer'),
)
    
bill_status_choice = (
    ('Paid','Paid'),
    ('Pending','Pending'),
    ('Partially Paid','Partially Paid'),
)
class Bill(models.Model):
    company_name    = models.ForeignKey(Contact, on_delete=models.CASCADE)
    grand_tot       = models.DecimalField(max_digits=9, decimal_places=2)
    status          = models.CharField(max_length=100, choices=bill_status_choice)
lifecycle_status    = models.CharField(max_length=100, choices=lifecycle_status_choice) 
    ...

which has a fk to:

class Contact(models.Model):
    associated_company  = models.CharField(max_length=100) 
    ...

In my Views.py
...
from bills.models import Bill
from django.db.models import Sum

def home(request):
    data = Bill.objects.all().order_by('date')
    context = {
        'data ':data ,
    }
    return render(request, 'home.html',context)

Front end chartsjs:

const ctx = document.getElementById('myChart').getContext('2d');
        const myChart = new Chart(ctx, {
            type: 'doughnut',
            data: {
                labels: [
                    {% for data in data %} '{{data.company_name}}', {% endfor %}
            ],
        datasets: [{
            label: '# of Votes',
            data: [{% for data in data %} {{ data.grand_tot }}, {% endfor %}],

The problem: I am using charts.js in my front end template and I am trying to display a chart in which as a user creates new company names the chart should display the total amount grouped by company names how would I achieve that?

eg: I create two invoices for "xyz" company with amounts: $100 & $200 || & create one invoice for "abc" company with amount $100 I want the chart to group the "xyz" invoice amounts and display $300 (ie:100 200) || & "abc" = $100

FYI I'm aware I'm using something completely wrong in the view function but its just something that I tried. Thanks

CodePudding user response：

You can .annotate(…) [Django-doc] with:

from django.db.models import Sum

Contact.objects.annotate(bills_total=Sum('bill__grand_tot'))

The Contact objects that arise from this QuerySet will have an extra attribute .bills_total.

You can filter this with as satus paid with:

from django.db.models import Q, Sum

contacts = Contact.objects.annotate(bills_total=Sum('bill__grand_tot', filter=Q(bill__status='Paid')))

You can thus for example render this with:

{% for contact in contacts %}
    {{ contact.associated_company }}: {{ contact.bills_total }}
{% endfor %}

or convert it to a list of Python dictionaries for example and then return it as a JSON blob.

CodePudding user response：

after trying a couple of solutions I found a one that works for me I'm not really sure if this is the best way to accomplish it but posting it anyway just in case some1 can benefit/help improve it.

so my models remains unchanged as posted in the question. my updated views .py is as follows:

def home(request):
    contacts = Contact.objects.filter(lifecycle_status='Customer')
    data2 = Bill.objects.values('company_name__associated_company').order_by('company_name__associated_company').annotate(total_price=Sum('grand_tot')).filter(status='Paid')
context = {
        'contacts':contacts,
        'data2':data2,
    }
return render(request, 'home.html',context)

my frontend chartsjs :

const ctx = document.getElementById('myChart').getContext('2d');
        const myChart = new Chart(ctx, {
            type: 'doughnut',
            data: {
                labels: [
                    {% for contacts in contacts %} '{{contacts.associated_company}}', {% endfor %}
            ],
        datasets: [{
            label: '# of Votes',
            data: [{% for data2 in data2 %} {{ data2.total_price }}, {% endfor %}],