Django中如何筛选余额大于0或小于0的客户列表？

我已经创建了一个供React前端调用的API，现有一个CustomerViewSet可以正常展示所有客户列表，retrieve方法也能展示单个客户详情及已购订单，还会计算客户余额。现在需要实现获取所有余额大于0或小于0的客户列表，请问有什么实现思路？

class CustomerViewSet(viewsets.ViewSet):
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsAuthenticated]

    def list(self, request):
        customer = Customer.objects.all()
        serializer = CustomerSerializer(customer, many=True, context={"request": request})
        response_dict = {"error": False, "message": "All Customers List Data", "data": serializer.data}
        return Response(response_dict)

    def create(self, request):
        try:
            serializer = CustomerSerializer(data=request.data, context={"request": request})
            serializer.is_valid(raise_exception=True)
            serializer.save()
            dict_response = {"error": False, "message": "Customer Data Stored Successfully"}
        except:
            dict_response = {"error": True, "message": "Phone Number Exists In Database, Fill All Fields"}

        return Response(dict_response)

    def retrieve(self, request, pk=None):
        queryset = Customer.objects.all()
        customer = get_object_or_404(queryset, pk=pk)
        serializer = CustomerSerializer(customer, context={"request": request})

        serializer_data = serializer.data
        # Accessing All the Orders Details of Current Customer
        orders_details = Orders.objects.filter(customer_id=serializer_data["id"]).order_by('-id')
        orders_details_serializers = OrdersSerializer(orders_details, many=True)
        serializer_data["orders"] = orders_details_serializers.data

        # Accessing All Orders of Current Customer
        orders_count = Orders.objects.filter(customer_id=serializer_data["id"])
        orders_count_serializer = OrdersSerializer(orders_count, many=True, context={"request": request})

        # Total orders amount of current customer
        orders_total = Orders.objects.filter(customer_id=serializer_data["id"])
        amount = 0
        discount = 0
        kgs = 0
        for total in orders_total:
            amount = amount + float(total.amount)
            discount = discount + float(total.discount)
            kgs = kgs + float(total.kgs)

        serializer_data1 = serializer.data
        # Accessing All the Payment Details of Current Customer
        payments_details = Payments.objects.filter(customer_id=serializer_data1["id"]).order_by('-id')
        payments_details_serializers = PaymentsSerializer(payments_details, many=True)
        serializer_data["payments"] = payments_details_serializers.data

        serializer_data2 = serializer_data
        payment_count = Payments.objects.filter(customer_id=serializer_data2["id"])
        payment_count_serializer = PaymentsSerializer(payment_count, many=True, context={"request": request})

        # Total Payment of current customer
        payment_total = Payments.objects.filter(customer_id=serializer_data2["id"])
        t_amount = 0
        for balance in payment_total:
            t_amount = t_amount + float(balance.payment)

        balance = amount - t_amount

        dict_response = {"error": False, "message": "Single Data Fetch",
                         "data": serializer_data,
                         "payment": len(payment_count_serializer.data),
                         "buy_total": amount,
                         "payed_total": t_amount,
                         "balance": balance,
                         "kgs": kgs,
                         "discount": discount,
                         "orders_count": len(orders_count_serializer.data)}
        return Response(dict_response)

    def update(self, request, pk=None):
        try:
            queryset = Customer.objects.all()
            customer = get_object_or_404(queryset, pk=pk)
            serializer = CustomerSerializer(customer, data=request.data, context={"request": request})
            serializer.is_valid(raise_exception=True)
            serializer.save()
            dict_response = {"error": False, "message": "Customer Data Updated Successfully"}
        except:
            dict_response = {"error": True, "message": "An Error Occurred"}

        return Response(dict_response)

    def destroy(self, request, pk=None):
        queryset = Customer.objects.all()
        customer = get_object_or_404(queryset, pk=pk)
        # serializer = PaymentsSerializer(customer, context={"request": request})
        customer.delete()
        return Response({"error": False, "message": "Customer Deleted"})

思路一：数据库层面计算并过滤（推荐） #

直接通过ORM聚合查询计算每个客户的余额，再过滤符合条件的记录，效率远高于内存计算，适合大数据量场景。

实现要求 #

确保Customer与Orders、Payments模型有正确外键关联，比如：

• Orders模型：customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='orders')
• Payments模型：customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='payments')

修改后的list方法 #

from django.db.models import Sum, F

def list(self, request):
    # 接收前端过滤参数，比如?balance=gt0（余额>0）或balance=lt0（余额<0）
    balance_filter = request.query_params.get('balance', None)
    
    # 聚合计算每个客户的总订单额、总支付额、余额
    customers_queryset = Customer.objects.annotate(
        total_order_amount=Sum('orders__amount', default=0.0),
        total_payment=Sum('payments__payment', default=0.0),
        balance=F('total_order_amount') - F('total_payment')
    )
    
    # 应用过滤条件
    if balance_filter == 'gt0':
        customers_queryset = customers_queryset.filter(balance__gt=0)
    elif balance_filter == 'lt0':
        customers_queryset = customers_queryset.filter(balance__lt=0)
    
    serializer = CustomerSerializer(customers_queryset, many=True, context={"request": request})
    
    # 给序列化数据添加余额字段
    for idx, customer in enumerate(customers_queryset):
        serializer.data[idx]['balance'] = customer.balance
    
    response_dict = {
        "error": False, 
        "message": "Filtered Customers List Data", 
        "data": serializer.data
    }
    return Response(response_dict)

思路二：内存中过滤（仅适合小数据量） #

若暂时不想修改数据库查询逻辑，可先获取所有客户，在内存中计算余额后过滤。但数据量大时性能差，不推荐。

修改后的list方法 #

from django.db.models import Sum

def list(self, request):
    balance_filter = request.query_params.get('balance', None)
    customers = Customer.objects.all()
    serializer = CustomerSerializer(customers, many=True, context={"request": request})
    filtered_data = []
    
    for customer_data in serializer.data:
        # 计算当前客户的总订单额和总支付额
        total_order = Orders.objects.filter(customer_id=customer_data['id']).aggregate(Sum('amount'))['amount__sum'] or 0.0
        total_payment = Payments.objects.filter(customer_id=customer_data['id']).aggregate(Sum('payment'))['payment__sum'] or 0.0
        balance = total_order - total_payment
        
        # 根据条件筛选
        if (balance_filter == 'gt0' and balance > 0) or (balance_filter == 'lt0' and balance < 0) or not balance_filter:
            customer_data['balance'] = balance
            filtered_data.append(customer_data)
    
    response_dict = {
        "error": False, 
        "message": "Filtered Customers List Data", 
        "data": filtered_data
    }
    return Response(response_dict)

额外优化建议 #

现有retrieve方法多次重复查询Orders和Payments，可合并为聚合查询减少数据库请求：

# 替换retrieve中的循环计算部分
order_agg = Orders.objects.filter(customer_id=customer.id).aggregate(
    total_amount=Sum('amount', default=0.0),
    total_discount=Sum('discount', default=0.0),
    total_kgs=Sum('kgs', default=0.0),
    order_count=Count('id')
)
payment_agg = Payments.objects.filter(customer_id=customer.id).aggregate(
    total_payment=Sum('payment', default=0.0),
    payment_count=Count('id')
)

amount = order_agg['total_amount']
discount = order_agg['total_discount']
kgs = order_agg['total_kgs']
orders_count = order_agg['order_count']
t_amount = payment_agg['total_payment']
payment_count = payment_agg['payment_count']
balance = amount - t_amount

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