Django filter with annotate

Question

class Review(models.Model):
    slug = models.SlugField(max_length=255, unique=True)
    vendor = models.ForeignKey(Vendor)
    user = models.ForeignKey(User, blank=True, null=True)
    product = models.ForeignKey(Product, blank=True, null=True)
    images = models.ManyToManyField(ReviewImage, blank=True, null=True)
    headline = models.CharField(max_length=100)
    review = models.TextField(blank=True, null=True)
    rating = models.IntegerField()
    active = models.BooleanField(default=1)
    created = models.DateTimeField(auto_now_add=True)
    changed = models.DateTimeField(auto_now=True)

# This is the problem... I works, but no vendor is shown if there is no review.
vendor_list = (Vendor.objects.filter(category=category,
                                     review__product__isnull=True,
                                     active=True)
               .annotate(rating_avg=Avg('review__rating')))

HOW can I do it with review__product__isnull=True? If there is no review at all, I still want the vendor, but the rating should be: "0", .. what to do?

Possible duplicate of stackoverflow.com/questions/553038/… — Mariusz Jamro
– Mariusz Jamro, Commented Mar 21, 2012 at 7:58
Can you detail what you are trying to do ? it seems like you are trying to get vendors without review but the use of Avg is confusing. — jpic
– jpic, Commented Mar 21, 2012 at 9:53
I want to list all vendors i the category, and show the rating for each ... but I only want the rating where a "product" is not selected .. Thank for helping! — pkdkk
– pkdkk, Commented Mar 21, 2012 at 10:29
@Secator: that's a very useful answer (both for the use of COALESCE and for the monkey-patching of Django's aggregates), but it's hard to apply in this case because of the need for an OUTER JOIN on the review's product field. (If you know a good way to express this in Django's query language, I'd love to see it.) — Gareth Rees
– Gareth Rees, Commented Mar 21, 2012 at 11:19

Gareth Rees · Accepted Answer · 2012-03-21 11:10:39Z

1

Let's see if I understand this. You are trying to list all active vendors in the category, annotated with the average rating of their reviews. The way you determine that a review is a vendor review rather than a product review is that the product field is null. And you want the average rating of vendors with no reviews to be zero.

In SQL your query requires an OUTER JOIN:

SELECT vendor.id, COALESCE(AVG(review.rating), 0.0) AS rating
  FROM myapp_vendor AS vendor
  LEFT OUTER JOIN myapp_review AS review
    ON review.vendor_id = vendor.id
       AND review.product IS NULL
  WHERE vendor.category = %s
    AND vendor.active
  GROUP BY vendor.id

Sometimes in Django the simplest solution is a raw SQL query: as the developers say, the database API is "a shortcut but not necessarily an end-all-be-all." So that would look like this:

for v in Vendor.objects.raw('SELECT ... ', [category]): # query as above
    print 'Vendor {0} has rating {1}'.format(v.name, v.rating)

edited Mar 21, 2012 at 11:10

answered Mar 21, 2012 at 10:49

Gareth Rees

66.1k10 gold badges139 silver badges166 bronze badges

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

1 Comment

pkdkk Over a year ago

Exactly what I was looking for ... thanks ;) ... Don't know if my "product" / "vendor" review solution is the best one?, to keep it in one table? .

Mikael · Accepted Answer · 2012-03-21 16:38:40Z

0

OK I might be wrong here. I did a small test and it gave me the correct result but I would have to spend more time testing and I don't have that now.

You could try this:

vendor_list = Vendor.objects.filter(category=category, active=True)
vendor_list = vendor_list.filter(Q(review__product__isnull=True)|Q(review__isnull=True)).annotate(rating_avg=Avg('review__rating'))

(The filter has been separated in to 2 lines to make it easier to read but could be merged)

The idea is that you first take all vendors and then filter those who either has no product reviews or no reviews at all. Then you annotate those.

The rating for those vendors missing a review would be None not 0.

answered Mar 21, 2012 at 16:38

Mikael

3,24424 silver badges21 bronze badges

Collectives™ on Stack Overflow

Django filter with annotate

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related