CODEWITHSUNDEEP
pythondjangodjango-queryset
pkdkk
pkdkk

Reputation: 3971

Django filter with annotate

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?

Upvotes: 0

Views: 1892

Answers (2)

Mikael
Mikael

Reputation: 3234

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.

Upvotes: 0

Gareth Rees
Gareth Rees

Reputation: 65884

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)

Upvotes: 1

Related Questions