Filtering a reverse lookup in Django template

Question

I have a model that looks like this

class Invoice(models.Model):
    inv_number = models.CharField(max_length=10, primary_key=True)
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT)
    inv_date = models.DateField()

class Txn(models.Model): 
    invoice = models.ForeignKey(Invoice, on_delete=models.PROTECT)
    transaction_date = models.DateField()
    reference = models.CharField(max_length=12)
    amt = models.IntegerField()

I would like to display a report in my template that lists filtered invoices each with a sub-list of filtered transactions.

In my view I have done the following:

invoice_list = Invoice.objects.filter(customer=customer)

Which I pass into my template. In the template, I do something like the following:

{% for invoice in invoice_list %}
   {{ invoice.inv_number }}, {{ invoice.customer}}, {{ invoice.inv_date }}
   {% for txn in invoice.txn_set.all %}
      {{ txn.transaction_date }}, {{ txn.reference }}, {{ txn.amt }}
   {% endfor %}
{% endfor %}

This works great to show the entire transaction list per filtered invoice. The question is, how does one also filter the list of transactions per invoice in the template - what if I wanted just the transactions within a certain date range or that match a specific reference? Is there maybe a way to pass a filter to the txn_set queryset per invoice in the view before putting the main queryset in the context and without converting them to lists?

Thank you for any response!

user_9225671 · Answer

Suggestion: collect the invoices and transactions in the view, not in the template.

With this view code you can reduce the amount of queries to 1, so it is a lot more optimal than your code (with queries the Txn table for each invoice):

# build basic query
qs = Txn.objects.select_related('invoice')\
    .filter(invoice__customer=customer)\
    .order_by('transaction_date')

# add filtering; here is an example, but it could be with date filter as well
reference = request.GET.get('reference', '')
if len(reference) > 0:
    qs = qs.filter(reference__icontains=reference)

invoice_dict = {}
for txn in qs:
    # add the Invoice if it does not exist yet
    if txn.invoice_id not in invoice_dict:
        invoice_dict[txn.invoice_id] = {
            'invoice': txn.invoice,
            'txn_list': [],
        }

    # add the Txn
    invoice_dict[txn.invoice_id]['txn_list'].append(txn)

# sort by Invoice date
invoice_data_list = sorted(
    invoice_dict.values(),
    key=lambda x: x['invoice'].inv_date)

Then in your template:

{% for elem in invoice_data_list %}
    {{ elem.invoice.inv_number }}
    {{ elem.invoice.customer}}
    {{ elem.invoice.inv_date }}

    {% for txn in elem.txn_list %}
         {{ txn.transaction_date }}
         {{ txn.reference }}
         {{ txn.amt }}
    {% endfor %}
{% endfor %}

Filtering a reverse lookup in Django template

1 Answers