django filter queryset based on count of another queryset

Question

Assume my models:

model book 
    charfield name

model review
    charfield bookname

Is there a way to filter/exclude the books with review count zero? I looked this up throughly on stackoverflow but couldn't find an answer. This is not a homework question. I have a queryset approx. size around 200,000, from which I concluded that it is not smart to filter by converting the queryset to a python list, filter, then convert back. Can someone help me with this? Thanks

Is there a reason your Review model has a charfield with a book name instead of a foreign key directly to the correct Book object? The filter would be easier to do if the models were actually linked. Also, it isn't clear from your question whether you want to keep or exclude the books with no reviews from your queryset. — ChidG
– ChidG, Commented Jan 24, 2017 at 5:11
@ChidG thanks for the reply. Say I have a huge amount of books and reviews, I was thinking that linking a review to a book every time when I add it would take too long because of the lookup time. Should linking them be the way to do it in favor of this? — verticese
– verticese, Commented Jan 24, 2017 at 14:49
Linking them via foreign key is definitely the standard, best practice way of solving this problem. The time to do a database join between the two tables is going to be far less trouble than the other problems caused by not linking them. For example, your database would need to be much larger (in disk space), because the book names will all need to be stored in multiple places. And what happens when you have more than one book with the same name? Or when someone enters a book name incorrectly in a review? I would suggest you read up a little on relational database design. — ChidG
– ChidG, Commented Jan 25, 2017 at 1:59

ChidG · Accepted Answer · 2017-01-25 02:30:38Z

3

Because I strongly recommend changing your models to include a foreign key from review to book (for the many other benefits of doing that) rather than attempting to solve the exact problem you've asked about, I'm going to give you a hint on how to write your models with a foreign key and then how to solve the problem you've asked about in the context of a foreign key relationship.

Here are the new models I think you should write:

from django.db import models

class Book(models.Model):
    name = models.Charfield()

class Review(models.Model):
    book = models.ForeignKey(Book, related_name='reviews')

Then, to filter for all books having any reviews you could do:

books_with_reviews = Book.objects.filter(reviews__isnull=False).distinct()

Or for books without reviews:

Book.objects.filter(reviews__isnull=True).distinct()

answered Jan 25, 2017 at 2:30

ChidG

3,2231 gold badge24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ittus · Accepted Answer · 2017-01-24 06:05:12Z

1

First, you can query list of book which has review, and then get compliment by using django exclude

bookname_has_review = Review.objects.all().distinct().values_list('bookname', flat=True)
book_not_have_review = Book.objects.all().exclude(name__in=bookname_has_review)

answered Jan 24, 2017 at 6:05

ittus

22.5k5 gold badges62 silver badges59 bronze badges

Collectives™ on Stack Overflow

django filter queryset based on count of another queryset

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related