CODEWITHSUNDEEP
pythondjango
Joey Coder
Joey Coder

Reputation: 3529

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.

Upvotes: 2

Views: 40

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477318

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

Upvotes: 4

neverwalkaloner
neverwalkaloner

Reputation: 47364

Try to use filter on annotated value:

from django.db.models import Count
organizers = Organizer.objects.annotate(
    attendees_count=Count("events__attendees", distinct=True)
).filter(attendees_count__gt=10).first()

Upvotes: 3

Related Questions