How to use .annotate() or .aggregate() to extract a count from Django QuerySet relations?

Question

I have the follow .model structure:

class ArtistWorkPlaceQuerySet(models.QuerySet):
    def with_related(self):
        return self.select_related('artist','work', 'place')

class ArtistWorkPlaceManager(models.Manager):
    pass

class PersonWorkPlace(models.Model):
    artist = models.ForeignKey(Artist, verbose_name=_('artist'), related_name='work', on_delete=models.CASCADE)
    work = models.ForeignKey(Work, verbose_name=_('work'), related_name='place', on_delete=models.CASCADE)
    place = models.ForeignKey(Place, verbose_name=_('place'), on_delete=models.CASCADE)

    objects = PersonWorkPlaceManager.from_queryset(PersonWorkPlaceQuerySet)()

class Work(models.Model):
    piece_type = models.CharField(max_length=100, null=True, blank=True) //This is like paintings or sculptures

class Artist(models.Model):
    name = models.CharField(max_length=100)

class Place(models.Model):
    name = models.CharField(max_length=200, null=True, blank=True)

Through this query I can get all of the work by this artist:

    works = PersonWorkPlace.objects.filter(person=self.kwargs['pk'])

How do I go further and search for the number (a count) of works of the same the 'piece_type' at a particular place by the same artist?

I would like to pass or extract from context for a particular view the following information:

Artist A has 2 painting and 2 sculpture at Place A and 4 paintings at Place B

'context': { (place: 'Place A', painting: '2', sculpture: '2'), (place: 'Place B', painting: '4') }

score 0 · Answer 1 · answered Oct 04 '19 at 12:51

I don't think i would setup the models like this but maybe there's a reason for that. This is what i would come up with:

from django.db.models import Count    
from .models import PersonWorkPlace, Work

artist_pk = 1

artist_places = PersonWorkPlace.objects \
                    .filter(artist__pk=artist_pk) \
                    .order_by('place') \
                    .values('artist', 'place') \
                    .annotate(works=Count('work')) \
                    .values_list('artist', 'place', 'place__name', 'artist__name')

if artist_places:
    print(artist_places[0][3])
    for place in artist_places:
        pieces = Work.objects \
            .filter(place__artist__pk=place[0], place__place__pk=place[1]) \
            .order_by('piece_type').values('piece_type') \
            .annotate(count=Count('piece_type'))
        print(artist_places[0][2], pieces)

How to use .annotate() or .aggregate() to extract a count from Django QuerySet relations?

1 Answers1