[Answered ]-In django, what is the optimal way to access data in a OnetoMany relationship to output to template?

by

1👍

✅

You can .annotate(…) [Django-doc] to do this at the database side:

from django.db.models import Sum
from django.shortcuts import render

def customer_view(request):
    customers = Customers.objects.annotate(
        total_spent=Sum('transactions__tx_amount')
    )
    context = {'customers' : customers}
    return render(request, load_template, context)

This will thus generate a query that looks like:

SELECT customers.*, SUM(transactions.tx_amount) AS total_spent
FROM customers
LEFT OUTER JOIN transactions ON transactions.customers_id = customers.id

and thus fetches all data in a single query.

If there are no transactions, total_spent will be None (NULL), not 0, you can use Coalesce [Django-doc] to use zero instead:

from django.db.models import Sum, Value
from django.db.models.functions import Coalesce
from django.shortcuts import render

def customer_view(request):
    customers = Customers.objects.annotate(
        total_spent=Coalesce(Sum('transactions__tx_amount'), Value(0))
    )
    context = {'customers' : customers}
    return render(request, load_template, context)
👤willeM_ Van Onsem

Leave a comment