如何优化Django中统计Dispatch状态数量的查询集函数？

Hey there! Let's fix that performance bottleneck in your function. The core issue right now is that you're fetching every single status_id record into Python and counting them with loops—this gets really inefficient as your dataset grows. Instead, we can leverage Django's database aggregation tools to let the database handle the counting work, which is what databases are optimized for.

The Optimized Approach #

Here's how to rewrite your function to cut down on query load and speed things up:

from django.db.models import Count

def get_types_count_display(self):
    # Directly filter Dispatch objects related to this instance, then aggregate counts by status_id
    status_counts = (
        Dispatch.objects
        .filter(id__in=self.route_dispatches.values_list('dispatch_id', flat=True))
        .values('status_id')
        .annotate(count=Count('id'))
        .order_by()
    )

    # Convert the queryset into a dictionary for easy lookup
    count_map = {item['status_id']: item['count'] for item in status_counts}

    # Return counts with defaults for each status (0 if no records exist)
    return {
        'pendents': count_map.get(1, 0),
        'delivered': count_map.get(2, 0),
        'partial': count_map.get(3, 0),
        'undelivered': count_map.get(4, 0)
    }

Why This Works Better #

• Fewer Database Hits: Your original code makes two separate queries (one to get dispatch IDs, another to fetch the Dispatch objects), then processes all records in Python. This optimized version does a single aggregated query that returns only the counts you need.
• Database-Level Aggregation: Databases are built to handle counting and grouping efficiently—this is way faster than iterating through hundreds/thousands of records in Python, especially as your data scales.
• Cleaner Handling of Empty Cases: Using dict.get() ensures that even if there are no dispatches (like when dispatches_types is empty), each status count defaults to 0 instead of causing errors.

Bonus: Even More Optimization #

If your route_dispatches is a related manager (i.e., a ForeignKey/ManyToMany relationship between your model and Dispatch), you can simplify the filter even further to avoid the values_list call entirely:

# Assuming route_dispatches is a related manager pointing to Dispatch
status_counts = (
    self.route_dispatches.all()
    .values('status_id')
    .annotate(count=Count('id'))
    .order_by()
)

This cuts out the intermediate id__in filter and makes the query even more efficient by using Django's built-in relationship handling.

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