Sunday, 1 May 2022

Using annotate and distinct(field) together in Django

I've got a bunch of reviews in my app. Users are able to "like" reviews.

I'm trying to get the most liked reviews. However, there are some popular users on the app, and all their reviews have the most likes. I want to only select one review (ideally the most liked one) per user.

Here are my objects,

class Review(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='review_user', db_index=True)
    review_text = models.TextField(max_length=5000)
    rating = models.SmallIntegerField(
        validators=[
            MaxValueValidator(10),
            MinValueValidator(1),
        ],
    )
    date_added = models.DateTimeField(db_index=True)
    review_id = models.AutoField(primary_key=True, db_index=True)

class LikeReview(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='likereview_user', db_index=True)
    review = models.ForeignKey(Review, on_delete=models.CASCADE, related_name='likereview_review', db_index=True)
    date_added = models.DateTimeField()

    class Meta:
        unique_together = [['user', 'review']]

And here's what I currently have to get the most liked reviews:

reviews = Review.objects.filter().annotate(
    num_likes=Count('likereview_review')
).order_by('-num_likes').distinct()

As you can see, the reviews I get will be sorted by the most likes, but its possible that the top liked reviews are all by the same user. I want to add distinct('user') here but I get annotate() + distinct(fields) is not implemented.

How can I accomplish this?



from Using annotate and distinct(field) together in Django

No comments:

Post a Comment