So I have a social media app, where users can like the posts of other users. Now I want to fetch the top 20 users who have received the most number of likes. I am pretty much confused how to query my Likes Model
My LIKES MODEL
class PostLike(models.Model): user_who_liked = models.ForeignKey(User, on_delete=models.CASCADE) post_liked = models.ForeignKey(Post, on_delete=models.CASCADE) liked_on = models.DateTimeField(default=timezone.now)
SIMPLIFIED POST MODEL
class Post(models.Model): id = models.AutoField(primary_key=True) user = models.ForeignKey(User, on_delete=models.CASCADE) caption = models.TextField() date = models.DateTimeField(default=timezone.now) likes = models.ManyToManyField( User, blank=True, through=PostLike) image = models.TextField() class Meta: ordering = ['-id']
SIMPLIFIED USER MODEL
class User(AbstractBaseUser, PermissionsMixin): email = models.EmailField(unique=True) user_name = models.CharField(max_length=100, unique=True) date = models.DateTimeField(default=timezone.now) profile_picture = models.TextField( default="https://www.kindpng.com/picc/m/24-248253_user-profile-default-image-png-clipart-png-download.png") bio = models.CharField(max_length=200, default="") objects = CustomManger() def __str__(self): return self.user_name
** My View **
@api_view(["GET"]) @permission_classes([IsAuthenticated]) def leaderboard(request): # I dont know how to query the PostLike model now to get the most liked users pass
Advertisement
Answer
First I changed the user
attribute in your Post
model, I added related_name
because otherwise the related names were clashing. This is the definition I used, otherwise your models are unchanged.
user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='author')
I.e. the posts by a user are accessible on User
via the author
attribute.
The following query gives you the top 20 users by number of likes they received:
User.objects.annotate(num_likes=Count('author__likes')).order_by('-num_likes')[:20]
Explanation:
- Query
User
model and - annotate each user by doing a count:
author
leads to the posts by the userlikes
follows toPostLike
and counts all likes which are associated with a post by the user
- then order by number of likes descending,
- and limit the number of retrieved objects to 20.