CODEWITHSUNDEEP
pythondjangodjango-models
Krishna
Krishna

Reputation: 349

Referencing twice related field for django model query

So I am using Django to construct a Query and I have 3 models as defined:

class Book(models.Model):
    ...


class Upload(models.Model):
    ...
    book = models.ForeignKey(Book, on_delete=models.CASCADE)


class Region(models.Model):
    ...
    page = models.ForeignKey(Upload, on_delete=models.CASCADE)

Given these 3 models I wanted a query that lists all the books and annotate them with a segmented_pages variable that contains the count of all the Upload that have non-zero number of regions.

Basically, counting the number of uploads per book that have atleast one region.

I am assuming the basic structure of the query would look like this and mainly the logic inside filter needs to be modified as there is no convenient count lookup.

Book.objects.annotate(segmented_pages=Count('upload', filter=Q(upload__region__count__gt=0))

Can someone please help me with the logic of the filter and a simple explanation of how to go about designing these types of queries using django models?

Upvotes: 1

Views: 40

Answers (1)

Abdul Aziz Barkat
Abdul Aziz Barkat

Reputation: 21787

You can rewrite "non-zero number of regions" as "In the join produced by the query, the region for any upload must not be null", hence you can simply use isnull [Django docs]:

from django.db.models import Count, Q


Book.objects.annotate(
    segmented_pages=Count(
        'upload',
        filter=Q(upload__region__isnull=False),
        distinct=True
    )
)

Upvotes: 1

Related Questions