Django视图模板开发求助：将查询集转为指定格式表格

需要将视图返回的支付模式月度汇总数据，渲染成按支付模式为行、月份为列的表格，缺失数据的单元格留空。

当前查询集 #

{'totals': 
<QuerySet [
{'trans_mode': 'Cash', 'month': datetime.date(2023, 1, 1), 'tot': Decimal('99.25')}, 
{'trans_mode': 'Cash', 'month': datetime.date(2023, 2, 1), 'tot': Decimal('161.25')}, 
{'trans_mode': 'Cash', 'month': datetime.date(2023, 3, 1), 'tot': Decimal('40.5')}, 
{'trans_mode': 'ENBD', 'month': datetime.date(2023, 1, 1), 'tot': Decimal('2215.72000000000')}, 
{'trans_mode': 'ENBD', 'month': datetime.date(2023, 2, 1), 'tot': Decimal('1361.66000000000')}, 
{'trans_mode': 'ENBD', 'month': datetime.date(2023, 3, 1), 'tot': Decimal('-579.130000000000')}, 
{'trans_mode': 'NoL', 'month': datetime.date(2023, 1, 1), 'tot': Decimal('107')}, 
{'trans_mode': 'NoL', 'month': datetime.date(2023, 2, 1), 'tot': Decimal('56')}, 
{'trans_mode': 'NoL', 'month': datetime.date(2023, 3, 1), 'tot': Decimal('-69.5')}, 
{'trans_mode': 'Pay IT', 'month': datetime.date(2023, 1, 1), 'tot': Decimal('0')}, 
{'trans_mode': 'SIB', 'month': datetime.date(2023, 1, 1), 'tot': Decimal('208.390000000000')}, 
{'trans_mode': 'SIB', 'month': datetime.date(2023, 2, 1), 'tot': Decimal('-3.25')}
]>}

Models.py代码 #

class Transactions(models.Model):
    TRANSACTION_TYPE_CHOICES = (
        ('income', 'Income'),
        ('expense', 'Expense'),
    )

    PAYMENT_MODE_CHOICES = (
        ('cash', 'Cash'),
        ('enbd', 'ENBD'),
        ('nol', 'NOL'),
        ('payit', 'Pay IT'),
        ('sib', 'SIB'),
    )

    trans_id = models.AutoField(primary_key=True)
    trans_date = models.DateField(verbose_name="Date", db_index=True)
    trans_type = models.CharField(max_length=10, choices=TRANSACTION_TYPE_CHOICES, verbose_name="Type")
    trans_main_category = models.ForeignKey(MainCategory, on_delete=models.CASCADE, verbose_name="Main Category",
                                            related_name="transactions")
    trans_sub_category = models.ForeignKey(SubCategory, on_delete=models.CASCADE, verbose_name="Sub Category",
                                           related_name="transactions")
    trans_mode = models.CharField(max_length=10, choices=PAYMENT_MODE_CHOICES, verbose_name="Payment Mode",
                                  db_index=True)
    trans_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="Amount")

    objects = models.Manager()

    class Meta:
        verbose_name = "Transaction"
        verbose_name_plural = "Transactions"

    def __str__(self):
        amount = self.trans_amount
        if self.trans_type == 'expense':
            amount = -amount
            amount_str = f"${amount:,.2f}"

        return (
            f"Transaction ID: {self.trans_id}\n"
            f"Date: {self.trans_date}\n"
            f"Type: {self.trans_type}\n"
            f"Main Category: {self.trans_main_category}\n"
            f"Sub Category: {self.trans_sub_category}\n"
            f"Payment Mode: {self.trans_mode}\n"
            f"Amount: ${self.trans_amount:,.2f}"
        )

Views.py代码 #

def transaction_summary(request):
    totals = Transaction.objects.annotate(month=TruncMonth('trans_date')).values(
   'month', 'trans_mode').annotate(tot=Sum('trans_amount')).order_by()
    context = {
              'totals': totals
              }
     return render(request, 'transaction_summary.html', context)

期望渲染效果 #

实现方案 #

方案一：视图层预处理数据（推荐） #

修改views.py，将原始查询集转换为更易渲染的结构化数据，同时自动获取所有支付模式和月份：

from django.db.models import TruncMonth, Sum
from django.shortcuts import render
from .models import Transactions

def transaction_summary(request):
    # 获取原始汇总数据
    totals = Transactions.objects.annotate(month=TruncMonth('trans_date')).values(
        'month', 'trans_mode').annotate(tot=Sum('trans_amount')).order_by('month', 'trans_mode')
    
    # 提取所有唯一月份（格式化显示为"Jan 2023"样式）
    months = sorted({item['month'].strftime('%b %Y') for item in totals})
    # 获取所有支付模式（从模型choices中提取全称，确保包含无数据的模式）
    all_trans_modes = [mode[1] for mode in Transactions.PAYMENT_MODE_CHOICES]
    
    # 构建支付模式-月份的二维数据结构，初始化所有单元格为空
    mode_data = {}
    for mode in all_trans_modes:
        mode_data[mode] = {month: "" for month in months}
    
    # 填充已有数据，0值显示为空
    for item in totals:
        month_str = item['month'].strftime('%b %Y')
        mode = item['trans_mode']
        amount = round(item['tot'], 2)
        mode_data[mode][month_str] = amount if amount != 0 else ""
    
    context = {
        'mode_data': mode_data,
        'months': months
    }
    return render(request, 'transaction_summary.html', context)

模板代码（transaction_summary.html）

需要先添加自定义模板过滤器来获取字典值：

1. 在app目录下创建templatetags文件夹，新增custom_filters.py：

from django import template

register = template.Library()

@register.filter
def get_item(dictionary, key):
    return dictionary.get(key, "")

2. 模板中加载过滤器并渲染表格：

{% load custom_filters %}

<div class="s-table-container">
    <table class="s-table">
        <thead>
            <tr>
                <th>Trans Mode</th>
                {% for month in months %}
                <th>{{ month }}</th>
                {% endfor %}
            </tr>
        </thead>
        <tbody>
            {% for mode, amounts in mode_data.items %}
            <tr>
                <td>{{ mode }}</td>
                {% for month in months %}
                <td>{{ amounts|get_item:month }}</td>
                {% endfor %}
            </tr>
            {% endfor %}
        </tbody>
    </table>
</div>

方案二：模板层直接处理数据（仅适用于固定月份场景） #

如果不想修改视图，可在模板中通过分组和条件判断渲染，但扩展性较差：

<div class="s-table-container">
    <table class="s-table">
        <thead>
            <tr>
                <th>Trans Mode</th>
                <th>Jan 2023</th>
                <th>Feb 2023</th>
                <th>Mar 2023</th>
            </tr>
        </thead>
        <tbody>
            {% regroup totals by trans_mode as mode_groups %}
            {% for group in mode_groups %}
            <tr>
                <td>{{ group.grouper }}</td>
                <!-- 匹配1月份数据 -->
                <td>{% for item in group.list %}{% if item.month == date(2023,1,1) %}{{ item.tot|floatformat:2 }}{% endif %}{% endfor %}</td>
                <!-- 匹配2月份数据 -->
                <td>{% for item in group.list %}{% if item.month == date(2023,2,1) %}{{ item.tot|floatformat:2 }}{% endif %}{% endfor %}</td>
                <!-- 匹配3月份数据 -->
                <td>{% for item in group.list %}{% if item.month == date(2023,3,1) %}{{ item.tot|floatformat:2 }}{% endif %}{% endfor %}</td>
            </tr>
            {% endfor %}
            <!-- 手动添加无数据的支付模式 -->
            <tr>
                <td>Pay IT</td>
                <td></td>
                <td></td>
                <td></td>
            </tr>
        </tbody>
    </table>
</div>

内容的提问来源于stack exchange，提问作者Jamal A M