Skip to content
Advertisement

Filter for > 10 entries in foreign key object

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())

You can see here I have three queries to return the first event that matches the criteria > 10 attendees. I wonder if there is a better way to combine this logic in one or two querysets instead.

Advertisement

Answer

Since , you can make use of .alias(…) [Django-doc] for annotations that you do not need in the SELECT … part.

We thus can filter with:

Organizer.objects.alias(
    nattendees=Count('events__attendees')
).filter(
    nattendees__gt=10
).first()

We here count the same attendee multiple times if that attendee visits multiple events of that organizer. If we want to count distinct attendees, you should add distinct=True to the Count expression.

If you use or older, then you replace .alias(…) with .annotate(…) [Django-doc].

Since we did not specify an order (and assuming the Organizer model has no ordering option), it will retrieve a Organizer for which the condition holds, not per se the same Organizer each time.

For the Organizer with the lowest primary key that satisfies the condition, we thus order with:

#                    ↓ annotate if prior to Django-3.2
Organizer.objects.annotate(
    nattendees=Count('events__attendees')
).filter(
    nattendees__gt=10
).earliest('pk')
Advertisement