Filter parent objects which have no children object using prefetch_related

user_10765455 0
Viewed 307

I have three models as following, with Seller as the grandparent, Genre as the parent and Book as the chidlren:

class Seller(models.Model):
    name = models.CharField(max_length=20)

    def __str__(self):
        return self.name

class Genre(models.Model):
    seller= models.ForeignKey(Seller, related_name="genre",  on_delete=models.CASCADE)
    name = models.CharField(max_length=20)

    def __str__(self):
        return self.name


class Book(models.Model):
    genre= models.ForeignKey(Genre, related_name="book",  on_delete=models.CASCADE)
    name = models.CharField(max_length=20)

    def __str__(self):
        return self.name

If I use prefetch_related() to fetch the Seller objects along with their Genre and Book as following in one single databse query:

sellers = Seller.objects.prefetch_related('genre__book').filter()

However, I would like to filter out Seller objects that have no Book objects related to. What would be the syntax for the filter() in this case?

django
1 Answers

To filter genres that have no books in it you need the following condition:

genres = Genre.objects.exclude(pk__in=[x.genre.pk for x in Book.objects.all()]

To combine it with prefetch_related I think you need to use Prefetch object with a given queryset

from django.db.models import Prefetch

sellers = Seller.objects.prefetch_related(
    Prefetch('genre_set', 
             queryset=Genre.objects.exclude(pk__in=[x.genre.pk for x in Book.objects.all()])
    )
user_136982940
Related