Django动态过滤多查询集：实现支出月度细分与日期动态筛选

• 需求：按费用类型生成月度支出明细，希望通过过滤表单动态输入开始和结束日期，无需为每个月重复编写代码。
• 当前现状：为每种费用类型创建了20多个独立查询集，每个月对应一个重复的视图函数，仅date__gte和date__lte参数不同。
• 已尝试：在项目其他模块使用django-filters，但此处无法实现。
• 问询：是否可通过django-filters或其他方法解决该问题？

相关代码 #

models.py #

class Expenses(models.Model):
    date = models.DateField()
    place_of_expense = models.ForeignKey(ConstructionSite, on_delete=models.CASCADE, blank=True, null=True)
    TYPE_OF_EXPENSE_CHOICES = (
        ("oil", "oil"),
        ("leasing", "leasing"),
        ("material", "material"),
        ("land leasing", "land leasing"),
        ("spare parts", "spare parts"),
        ("tools and equipment", "tools and equipment"),
        ("services", "services"),
        ("accountant expenses", "accountant expenses"),
        ("interest rates", "interest rates"),
        ("phones", "phones"),
        ("other", "other"),
        # 其他选项省略
    )
    # 注：原代码中IZBOR_VRSTE_TROSKA应为TYPE_OF_EXPENSE_CHOICES，已修正
    type_of_expense = models.CharField(blank=True, null=True, max_length=25, choices=TYPE_OF_EXPENSE_CHOICES, default="oil")
    expense_description = models.CharField(max_length=255)
    unit = models.CharField(blank=True, null=True, max_length=20, choices=IZBOR_JEDINICE_MERE, default="l")
    quantity = models.DecimalField(max_digits=10, decimal_places=2)
    price_per_unit = models.DecimalField(max_digits=10, decimal_places=2)
    supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE, blank=True, null=True)

views.py（原重复代码示例） #

def monthly_analysis_november(request):
    accountant = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "accountant expenses")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_accountant=Sum('price'))
        
    interest_rates = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "interest rates")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_interest_rates=Sum('price'))
        
    phones = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "phones")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_phones=Sum('price'))
        
    oil = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "oil")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_oil=Sum('price'))
        
    leasing = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "leasing")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_leasing=Sum('price'))
        
    other_expenses = Expenses.objects\
    .filter(date__gte = "2023-11-01", date__lte = "2023-11-30", type_of_expense = "other")\
    .annotate(price=Sum(F('quantity')*F('price_per_unit')))\
        .aggregate(total_other_expenses=Sum('price'))
    # 后续处理省略

两种方法都能彻底解决重复代码问题，支持动态日期过滤：

方法一：不依赖django-filters，手动处理参数 #

用一个通用视图替代所有月度视图，通过请求参数获取日期范围，一次性统计所有费用类型支出。

优化后的views.py #

from django.db.models import Sum, F
from django.shortcuts import render
from .models import Expenses

def expense_analysis(request):
    # 从GET请求中获取日期参数，可设置默认值（比如当月）
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    
    # 构建基础查询集，动态过滤日期
    queryset = Expenses.objects.all()
    if start_date:
        queryset = queryset.filter(date__gte=start_date)
    if end_date:
        queryset = queryset.filter(date__lte=end_date)
    
    # 按费用类型分组统计总支出，替代20+个独立查询
    expense_totals = queryset.values('type_of_expense')\
        .annotate(total=Sum(F('quantity') * F('price_per_unit')))\
        .order_by('type_of_expense')
    
    # 转换为字典，方便模板直接按费用类型取值
    totals_dict = {item['type_of_expense']: item['total'] for item in expense_totals}
    
    context = {
        'expense_totals': expense_totals,
        'totals_dict': totals_dict,
        'start_date': start_date,
        'end_date': end_date
    }
    return render(request, 'expense_analysis.html', context)

模板示例（expense_analysis.html） #

添加日期过滤表单：

<form method="get">
    <label for="start_date">开始日期:</label>
    <input type="date" id="start_date" name="start_date" value="{{ start_date }}">
    
    <label for="end_date">结束日期:</label>
    <input type="date" id="end_date" name="end_date" value="{{ end_date }}">
    
    <button type="submit">查询</button>
</form>

<h2>支出明细</h2>
<ul>
    {% for expense in expense_totals %}
        <li>{{ expense.type_of_expense }}: {{ expense.total }}</li>
    {% empty %}
        <li>无匹配的支出记录</li>
    {% endfor %}
</ul>

方法二：使用django-filters实现灵活过滤 #

如果之前配置失败，以下是正确的实现步骤：

1. 创建filters.py #

import django_filters
from .models import Expenses

class ExpenseFilter(django_filters.FilterSet):
    start_date = django_filters.DateFilter(field_name='date', lookup_expr='gte')
    end_date = django_filters.DateFilter(field_name='date', lookup_expr='lte')
    
    class Meta:
        model = Expenses
        fields = ['start_date', 'end_date', 'type_of_expense']  # 可添加其他过滤字段

2. 优化视图 #

from django.shortcuts import render
from .models import Expenses
from .filters import ExpenseFilter
from django.db.models import Sum, F

def expense_analysis(request):
    # 初始化过滤器，绑定请求参数和查询集
    filter = ExpenseFilter(request.GET, queryset=Expenses.objects.all())
    # 按费用类型分组统计
    expense_totals = filter.qs.values('type_of_expense')\
        .annotate(total=Sum(F('quantity') * F('price_per_unit')))\
        .order_by('type_of_expense')
    
    context = {
        'filter': filter,
        'expense_totals': expense_totals
    }
    return render(request, 'expense_analysis.html', context)

3. 模板中渲染过滤器表单 #

django-filters会自动生成表单，直接渲染即可：

<form method="get">
    {{ filter.form.as_p }}
    <button type="submit">查询</button>
</form>

<h2>支出明细</h2>
<ul>
    {% for expense in expense_totals %}
        <li>{{ expense.type_of_expense }}: {{ expense.total }}</li>
    {% empty %}
        <li>无匹配的支出记录</li>
    {% endfor %}
</ul>

• 用单条分组查询替代20+个独立查询，减少数据库请求，提升性能
• 通过请求参数动态获取日期范围，无需为每个月编写单独视图
• 两种方法都支持前端动态输入日期，完全满足需求

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