How to use annotate in query set to extract a count of type in a location?

Question

I can't seem to properly use annotate to extract the information I need from my models.

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

@Iain Shelvington Just the SQLite. But will probably move to postgres. Is there anyway to do it in queryset? — H C, Sep 08 '19 at 00:06

Iain Shelvington · Answer 1 · 2019-09-08T02:18:13.033

You can use the following query to get a count of piece types by place for an artist

Place.objects.filter(
    personworkplace__artist=artist
).values(
    'name',
    'personworkplace__work__piece_type'
).annotate(
    count=Count('personworkplace__work__piece_type')
)

One of the returned keys is "personworkplace__work__piece_type" which is a bit ugly, you can change this by using an annotation

Place.objects.filter(
    personworkplace__artist=artist
).annotate(
    piece_type=F('personworkplace__work__piece_type')
).values(
    'name',
    'piece_type'
).annotate(
    count=Count('piece_type')
)

EDIT (old answer below):

Given an artist you can use this query to get the values that you want

Place.objects.filter(
    personworkplace__artist=artist
).annotate(
    painting=Sum(Case(
        When(personworkplace__work__piece_type='painting', then=1),
        output_field=IntegerField()
    )),
    sculpture=Sum(Case(
        When(personworkplace__work__piece_type='sculpture', then=1),
        output_field=IntegerField()
    )),
).values('name', 'something', 'something_else')

Sum, Case, When can be used to conditionally count related objects based on a condition, this allows you to count specific works by their type

Thanks. I didn’t know about Case. But is there a way to be more general? To make piece_type unspecified? — H C, Sep 08 '19 at 02:02
This is great. Just one additional questions: is there a way to make the output a dictionary where painting and sculpture from the same place is under the same key? — H C, Sep 08 '19 at 06:10

How to use annotate in query set to extract a count of type in a location?

1 Answers1