Django项目中如何在同一HTML表格展示存提款记录并优化性能

一、临时方案：不修改现有模型，合并记录展示 #

1. 视图层：统一数据结构并合并排序 #

在视图中将存款、取款记录转换为相同结构的字典，合并后按时间倒序排序，让模板只需循环一个统一列表：

def account_statement(request, id):
    try:
        customer = Account.objects.get(id=id)
        customer_id = customer.customer.id
        profile = customer.customer  # 提前获取Profile，避免模板重复查询
    except Account.DoesNotExist:
        messages.error(request, 'Something Went Wrong')
        return redirect('create-customer')
    
    # 转换存款记录为统一结构
    deposits = Deposit.objects.filter(customer__id=customer_id).order_by('-date')[:5]
    deposit_trans = [
        {
            'type': 'deposit',
            'acct': d.acct,
            'phone': profile.phone,
            'amount': d.deposit_amount,
            'date': d.date,
            'id': d.id
        } for d in deposits
    ]
    
    # 转换取款记录为统一结构
    withdrawals = Witdrawal.objects.filter(account__id=customer_id).order_by('-date')[:5]
    withdrawal_trans = [
        {
            'type': 'withdrawal',
            'acct': customer.acct_number,  # 假设Account存储了账号，或从Profile获取
            'phone': profile.phone,
            'amount': w.withdrawal_amount,
            'date': w.date,
            'id': w.id
        } for w in withdrawals
    ]
    
    # 合并并按时间倒序排序
    all_transactions = sorted(
        deposit_trans + withdrawal_trans,
        key=lambda x: x['date'],
        reverse=True
    )
    
    context = {
        'transactions': all_transactions,
        'customer': customer
    }
    return render(request, 'dashboard/statement.html', context)

2. 模板层：循环合并列表，区分交易类型 #

修改模板，根据交易类型展示不同样式（如存款金额标绿、取款标红），并跳转对应凭证页面：

<table class="table bg-white">
    <thead class="bg-info text-white">
        <tr>
            <th scope="col">#</th>
            <th scope="col">Acct. No.</th>
            <th scope="col">Phone</th>
            <th scope="col">Amount</th>
            <th scope="col">Date</th>
            <th scope="col">Action</th>
        </tr>
    </thead>
    <tbody>
        {% if transactions %}
            {% for trans in transactions %}
            <tr>
                <td>{{ forloop.counter }}</td>
                <td>{{ trans.acct }}</td>
                <td>{{ trans.phone }}</td>
                <td>
                    {% if trans.type == 'deposit' %}
                        <span class="text-success">+N{{ trans.amount | intcomma }}</span>
                    {% else %}
                        <span class="text-danger">-N{{ trans.amount | intcomma }}</span>
                    {% endif %}
                </td>
                <td>{{ trans.date | naturaltime }}</td>
                <td>
                    {% if trans.type == 'deposit' %}
                        <a class="btn btn-success btn-sm" href="{% url 'deposit-slip' trans.id %}">Slip</a>
                    {% else %}
                        <a class="btn btn-danger btn-sm" href="{% url 'withdrawal-slip' trans.id %}">Slip</a>
                    {% endif %}
                </td>
            </tr>
            {% endfor %}
        {% else %}
            <tr>
                <td colspan="6" style="text-align: center; color:red;">
                    No Transaction Found for {{ customer.customer.profile.surname }} {{ customer.customer.profile.othernames }}
                </td>
            </tr>
        {% endif %}
    </tbody>
</table>

二、长期最优方案：重构为统一交易模型 #

若追求极致性能（接近O(1)查询效率），建议将存款、取款合并为单表交易模型，减少数据库查询次数：

1. 重构模型 #

class Transaction(models.Model):
    TRANSACTION_TYPES = [
        ('deposit', 'Deposit'),
        ('withdrawal', 'Withdrawal'),
    ]
    customer = models.ForeignKey(Profile, on_delete=models.CASCADE)
    transID = models.CharField(max_length=12)
    acct = models.CharField(max_length=6)
    staff = models.ForeignKey(User, on_delete=models.CASCADE)
    amount = models.PositiveIntegerField()
    transaction_type = models.CharField(max_length=10, choices=TRANSACTION_TYPES)
    date = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return f'{self.customer} - {self.transaction_type.title()} - {self.amount}'
    
    # 添加联合索引，优化查询性能
    class Meta:
        indexes = [
            models.Index(fields=['customer', '-date']),
        ]

2. 简化视图查询 #

def account_statement(request, id):
    try:
        customer = Account.objects.get(id=id)
        customer_id = customer.customer.id
    except Account.DoesNotExist:
        messages.error(request, 'Something Went Wrong')
        return redirect('create-customer')
    
    # 一次查询获取所有交易，按时间倒序取前10条（可按需调整数量）
    transactions = Transaction.objects.filter(customer__id=customer_id).order_by('-date')[:10]
    
    context = {
        'transactions': transactions,
        'customer': customer
    }
    return render(request, 'dashboard/statement.html', context)

模板层只需根据transaction_type字段判断样式，逻辑和临时方案一致，更简洁。

三、性能优化要点 #

1. 索引优化：给关联字段+排序字段加联合索引（如Deposit的customer+date、Transaction的customer+date），避免数据库全表扫描。
2. 减少重复查询：视图中提前获取Profile等关联对象，避免模板中反复触发数据库查询。
3. 限制返回数量：用[:N]控制返回记录数，避免一次性加载过多数据。

四、替代for循环的表格展示方式 #

可使用django-tables2库自动生成表格，无需手动编写for循环：

1. 安装库：pip install django-tables2
2. 在settings.py中添加'django_tables2'到INSTALLED_APPS
3. 创建表格类：

# tables.py
import django_tables2 as tables
from .models import Transaction

class TransactionTable(tables.Table):
    amount = tables.Column(verbose_name='Amount')
    date = tables.DateTimeColumn(verbose_name='Date', format='n/j/Y H:i')
    action = tables.TemplateColumn(
        '<a class="btn btn-{{ record.transaction_type == "deposit" ? "success" : "danger" }} btn-sm" href="{% url record.transaction_type|add:"-slip" record.id %}">Slip</a>',
        verbose_name='Action'
    )

    class Meta:
        model = Transaction
        fields = ('acct', 'customer__phone', 'amount', 'date', 'action')
        template_name = 'django_tables2/bootstrap4.html'

4. 视图使用SingleTableView：

from django_tables2.views import SingleTableView
from .models import Transaction
from .tables import TransactionTable

class AccountStatementView(SingleTableView):
    model = Transaction
    table_class = TransactionTable
    template_name = 'dashboard/statement.html'
    paginate_by = 10

    def get_queryset(self):
        customer_id = Account.objects.get(id=self.kwargs['id']).customer.id
        return Transaction.objects.filter(customer__id=customer_id).order_by('-date')

5. 模板中渲染表格：

{% load django_tables2 %}
{% render_table table %}

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