Django中如何合并多QuerySet查询逻辑，以高效获取参会人数>10的首个活动

Original Code & Problem #

I currently have this code implementation:

organizers = Organizer.objects.filter(events__isnull=False).distinct()
for organizer in organizers:
    print("-----", organizer.name, "-----")
    events = organizer.events.all()
    for event in events:
        if not event.attendees.count() > 10:
            continue
        print(event.first())

As shown above, I'm currently making multiple database queries (one for organizers, one per organizer for events, and one per event for attendee counts) to get the first event that meets the condition of having more than 10 attendees. I want to optimize this implementation and find a feasible way to merge the above logic into 1 or 2 QuerySets to reduce the number of queries and improve efficiency.

Solution 1: Annotate + Prefetch Filtered Events (2 Queries Total) #

You can drastically cut down on database hits by moving the attendee count calculation to the database with annotate, and using Prefetch to only pull in the events that actually meet your criteria. Here's the optimized code:

from django.db.models import Count, Prefetch

# First, create a QuerySet for events with >10 attendees (annotates the count directly)
qualified_events = Event.objects.annotate(attendee_count=Count('attendees')).filter(attendee_count__gt=10)

# Fetch organizers, filter those who have at least one qualified event, and prefetch only those events
organizers = Organizer.objects.filter(events__in=qualified_events).distinct().prefetch_related(
    Prefetch('events', queryset=qualified_events, to_attr='qualified_events')
)

# Iterate without any extra database calls
for organizer in organizers:
    print("-----", organizer.name, "-----")
    if organizer.qualified_events:
        print(organizer.qualified_events[0])  # Grab the first qualifying event

This approach only runs 2 queries:

• One to get all events that meet the attendee count requirement
• One to fetch organizers and prefetch their qualified events (no per-organizer extra queries)

Solution 2: Annotate First Qualified Event Directly (1 Query Total) #

If you only care about the first qualifying event per organizer, you can use Subquery and OuterRef to attach that event directly to the organizer in a single query. This is the most efficient option:

from django.db.models import Count, Subquery, OuterRef

# Define a subquery to get the first event with >10 attendees for each organizer
first_qualified_event = Event.objects.annotate(
    attendee_count=Count('attendees')
).filter(
    attendee_count__gt=10,
    organizer=OuterRef('pk')
).order_by('id')[:1]  # Limit to the first event

# Fetch organizers with their first qualified event annotated (only 1 query!)
organizers = Organizer.objects.filter(
    events__isnull=False
).annotate(
    first_qualified_event=Subquery(first_qualified_event.values('id'))
).filter(
    first_qualified_event__isnull=False  # Exclude organizers with no qualifying events
).select_related('first_qualified_event')

# Loop through results without extra queries
for organizer in organizers:
    print("-----", organizer.name, "-----")
    print(organizer.first_qualified_event)

Why This Works: #

• Both solutions eliminate the N+1 query problem from your original code (where every organizer and event triggered extra database calls).
• Calculating the attendee count via annotate lets the database handle the heavy lifting, which is way more efficient than counting in Python.
• Prefetch and Subquery give you precise control over what data is fetched, so you don't waste resources on irrelevant events or counts.

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