Make group by having count(*) with django orm

Question

With following model:

class Day(models.Model):
    date = models.DateField(
        unique=True,
        null=False,
        blank=False,
    )


class Reservation(models.Model):
    user = models.ForeignKey(
        User,
        null=False,
        blank=False,
        related_name='reservations',
        verbose_name=_('Utilisateur'),
    )
    day = models.ForeignKey(
        Day,
        null=False,
        blank=False,
        related_name='reservations',
    )

To get days ids where number of reservations is lower or equal 5 i do:

SELECT day_id
FROM meal_reservation
GROUP BY day_id
HAVING count(*) <= 5

But how to do it with Django ORM ?

Answer 1

Read the docs about the combination of using values+annotate, this is what you need:

from django.db.models import Count

Reservation.objects.values('day').annotate(cnt=Count('id')).filter(cnt__lte=5)