django query with QuerySet objects

Question

assuming this is my models

class Organizer(models.Model):
     # properties
class Event(models.Model):
     organizer = models.ForeignKey(Organizer,on_delete=models.CASCADE)
     # other properties
class Ticket(models.Model):
     event = models.ForeignKey(Event,on_delete=models.CASCADE)
     # other properties
class Register(models.Model):
     ticket = models.ForeignKey(Ticket, on_delete=models.SET_NULL,  null=True)

I have a Organizer object org_obj and I want to get a list of all events that have more than 20 registers made for this Organizer

this is my code:

events = Event.objects.filter(organizer=org_obj)
event20 = []
    for e in events.iterator():
        tickets = Ticket.objects.filter(event=e)
        tickets_sold = 0
        for t in tickets.iterator():
            tickets_sold += Register.objects.filter(ticket=t).count()
        if tickets_sold > 20:
            event20.append(e)

is there to improve this query not using loops? In plain SQL this should be possible with join expressions and Subqueries.

Answer 1

You just annotate your Events with the number of Registers, and then filter on that, like:

from django.db.models import Count

event20 = org_obj.event_set.annotate(
    nregister=Count('ticket__register')
).filter(nregister__gt=20)

This is a QuerySet that contains the Events related to this Organizer with (strictly) more than 20 related Register objects. You can filter on nregister__gte=20 to obtain the Events with 20 or more (so 20 included) Registers.

Django will construct a query that looks similar to:

SELECT event.*,
       COUNT(register.id) AS nregister
FROM event
LEFT OUTER JOIN ticket ON event.id = ticket.event_id
LEFT OUTER JOIN register ON ticket.id = register.ticket_id
WHERE event.organizer_id = org_obj_id
GROUP BY event.id
HAVING COUNT(register.id) > 20

With org_obj_id the primary key value of the org_obj object.