Django实现唯一分类的金额在HTML表格单行展示

开发Django现金流页面时，若分类名称唯一，能否将该分类下的所有金额在HTML表格的同一行展示？当前代码通过ExtractMonth对交易日期进行月份注解，导致分类名称不再唯一，无法实现预期效果。

原始数据 #

"Car Payment"分类下有1月、2月两笔交易，金额均为-1400.03。

期望HTML输出 #

同一分类（如"Car Payment"）的各月金额和总计需展示在表格的同一行，即一行内包含分类名、1月金额、2月金额、总计金额。

原Views.py代码 #

class CashFlow(LoginRequiredMixin, AccountContextMixin, TemplateView):
    model = Transaction
    template_name = 'transaction_app/cf.html'
    context_object_name = 'transaction'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        user = self.request.user

        #-------下拉框逻辑开始---------
        # 获取年份选项
        distinct_years = Transaction.objects.filter(user=user).dates('transaction_date', 'year')
        year_choices = [(date.year, date.year) for date in distinct_years]
        unique_years = list(set(year_choices))
        unique_years.sort()
        context['unique_years'] = unique_years
        #-------下拉框逻辑结束-----------

        #---获取下拉框选中数据开始---------
        selected_year = self.request.GET.get('selected_year')
        selected_year = int(selected_year) if selected_year else datetime.now().year
    
        selected_status = self.request.GET.get('selected_status')
        #---获取下拉框选中数据结束-----------

        #----表格数据获取开始------------
        
        # 获取唯一分类名称
        category_names = Transaction.objects.filter(
            user=user,
            transaction_date__year=selected_year,
            status=selected_status
        ).values_list('category__category_name', flat=True).distinct()

        # 聚合每个分类的数据
        category_sums = []
        for category_name in category_names:
        # 按月份聚合当前分类的金额
            category_sum_by_month = Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
                category__category_name=category_name
            ).annotate(
                month=ExtractMonth('transaction_date')
            ).values('month').annotate(
                total_amount=Sum('amount')
            )

            # 将每月数据加入列表
            for item in category_sum_by_month:
                item['category__category_name'] = category_name
                category_sums.append(item)

            # 聚合当前分类的总金额
            category_sum = Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
                category__category_name=category_name
            ).aggregate(
                total_amount=Sum('amount')
            )

            category_sum['category__category_name'] = category_name
            category_sums.append(category_sum)
            category_sums.append(category_sum_by_month)

            context['category_sums'] = category_sums
        #----表格数据获取结束------------
        return context

原HTML模板代码 #

<table class="tbl-big" border="1">
    <thead>
        <tr>
            <th>Category Name</th>
            <th>Jan</th>
            <th>Feb</th>
            <th>Mar</th>
            <th>Total</th>
        </tr>
    </thead>
    <tbody>
        {% for item in category_sums %}
        <tr>
            <td>{{item.category__category_name}}</td>
            
            <td>
            {% if item.month == 1%}
            {{item.total_amount}}
            {% endif %}
            </td>
            
            <td>
            {% if item.month == 2%}
            {{item.total_amount}}
            {% endif %}
            </td>

            <td>
            {% if item.month == 3%}
            {{item.total_amount}}
            {% endif %}
            </td>

            <td>{{item.total_amount}}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

当前数据输出 #

{'month': 1, 'total_amount': Decimal('-1400.03000000000'), 'category__category_name': 'Car Payment'} 
{'month': 2, 'total_amount': Decimal('-1400.03000000000'), 'category__category_name': 'Car Payment'} 
{'total_amount': Decimal('-2800.06000000000'), 'category__category_name': 'Car Payment'} 
<QuerySet [{'month': 1, 'total_amount': Decimal('-1400.03000000000'), 'category__category_name': 'Car Payment'}, {'month': 2, 'total_amount': Decimal('-1400.03000000000'), 'category__category_name': 'Car Payment'}]>

解决方案（感谢willeM_ Van Onsem） #

调整后的Views.py代码 #

from collections import defaultdict, namedtuple
from django.db.models import F, ExtractMonth, Sum

# 封装分类汇总数据结构
CategoryOverview = namedtuple('CategoryOverview', ['name', 'entries', 'total'])

class CashFlow(LoginRequiredMixin, AccountContextMixin, TemplateView):
    model = Transaction
    template_name = 'transaction_app/cf.html'
    context_object_name = 'transaction'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        user = self.request.user

        # 保留原有下拉框逻辑
        distinct_years = Transaction.objects.filter(user=user).dates('transaction_date', 'year')
        year_choices = [(date.year, date.year) for date in distinct_years]
        unique_years = list(set(year_choices))
        unique_years.sort()
        context['unique_years'] = unique_years

        selected_year = self.request.GET.get('selected_year')
        selected_year = int(selected_year) if selected_year else datetime.now().year
        selected_status = self.request.GET.get('selected_status')

        # 重构数据聚合逻辑
        items = defaultdict(dict)
        sum_per_month = (
            Transaction.objects.filter(
                user=user,
                transaction_date__year=selected_year,
                status=selected_status,
            )
            .values(
                month=ExtractMonth('transaction_date'),
                category_name=F('category__category_name'),
            )
            .annotate(total_amount=Sum('amount'))
            .order_by('category_name', 'month')
        )

        # 按分类聚合各月数据
        for record in sum_per_month:
            items[record['category_name']][record['month']] = record['total_amount']
        
        # 组装模板需要的汇总数据
        context['summary'] = [
            CategoryOverview(key, [entries.get(month) for month in range(1, 13)], sum(entries.values()))
            for key, entries in items.items()
        ]

        return context

调整后的HTML模板代码 #

<table class="tbl-big" border="1">
    <thead>
        <tr>
            <th>分类名称</th>
            <th>1月</th>
            <th>2月</th>
            <th>3月</th>
            <!-- 可扩展添加4-12月表头 -->
            <th>总计</th>
        </tr>
    </thead>
    <tbody>
        {% for item in summary %}
        <tr>
            <td>{{item.name}}</td>
            <td>{{item.entries.0|default:"0"}}</td>
            <td>{{item.entries.1|default:"0"}}</td>
            <td>{{item.entries.2|default:"0"}}</td>
            <!-- 依次对应4-12月，索引为3到11 -->
            <td>{{item.total}}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

方案说明 #

1. 使用defaultdict按分类名称聚合各月数据，每个分类对应一个存储月份与金额的字典
2. 通过namedtuple封装分类名称、各月金额列表和总计金额，简化模板渲染逻辑
3. HTML模板直接遍历summary列表，每个条目对应表格一行，按索引取出对应月份金额，无数据则显示0

内容的提问来源于stack exchange，提问作者Mr Hobbit