SQL - requête complexe dans django (imbriqué) - Stack Overflow

I have a chain like this:

VENUE---FK---> EMPLOYEES---employee FK--->RESERVATIONS---reservation FK--->CHECKINS

and i can make:

employees = venue.employees.prefetch_related('reservations')

and then, for every employee in employees i need to get all the reservations(db is not hit because of the prefetch_related), but also, for every reservation i need to count all the checkins (this is the problematic part).

Is this possible with the django's ORM? How do you approach problems like this one, and avoid to generate 1000 queries?

You can use annotations for this. After you get all the reservations, you can get the count with single call using reservations. Somethign like:

res_ids = reservations.values_list('id',flat=True)
res_w_count = Reservation.object.filter(id__in=reservation_ids).annotate(Count('checkins'))

I have a chain like this:

VENUE---FK---> EMPLOYEES---employee FK--->RESERVATIONS---reservation FK--->CHECKINS

and i can make:

employees = venue.employees.prefetch_related('reservations')

and then, for every employee in employees i need to get all the reservations(db is not hit because of the prefetch_related), but also, for every reservation i need to count all the checkins (this is the problematic part).

Is this possible with the django's ORM? How do you approach problems like this one, and avoid to generate 1000 queries?

You can use annotations for this. After you get all the reservations, you can get the count with single call using reservations. Somethign like:

res_ids = reservations.values_list('id',flat=True)
res_w_count = Reservation.object.filter(id__in=reservation_ids).annotate(Count('checkins'))

Source