How to get highest value in django model for each objects-CodePudding

Admin wants to add different challenges. Each challenge has a lot of users. each user may have a lot of likes. I want to show the winner of each challenge. For that, I need to get which candidate gets the highest likes. How can I get it? is there any way like .count .? how can I use that? in which model.

For example:

challenges
1  first_contest 
2  second_contest 

candidates 
id name contest
1  jhon first_contest
2  sara second_contest
3  abi  first_contest

candidates likes
id user_id candidate_id
1  1       1
2  2       2
3  1       1

In this case candidate, 1 = Jhon get 2 likes so in the first contest Jhon wins. Also in the second contest, Sara gets 1 like. So I need to show the winner in the first contest. How is that?

models.py:

class Challenge(models.Model):
  title = models.CharField(max_length=50)

class Candidates(models.Model):
  owner = models.ForeignKey(User, on_delete=models.CASCADE)
  image = models.FileField( upload_to="challenge_candidates/",)
  def likes_count(self):
       return self.likes.all().count() 

class CandidateLikes(models.Model):
  like = models.CharField(max_length=10)
    user = 
  models.ForeignKey(User,on_delete=models.CASCADE,related_name='candidate_likes')
    contest_candidates = models.ForeignKey(Candidates, on_delete=models.CASCADE, 
  related_name='likes')

Sorry for my poor English. Thank you.

CodePudding user response：

You first need to have a relationship between your CandidatLike model and Challenge model so that you can filter by challenge. A foreign key relation could be sufficient. Then you can add this query to your views

winner = CandidateLikes.objects.filter(challenge="your_challenge_name").order_by("like")

Notice that challenge should exist in your CandidatLike model, since we are filtering by it.

CodePudding user response：

I think you are missing a relationship between Challenge and Candidates. Let's say you would add a challenge field to Candidate:

class Candidates(models.Model):
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    challenge = models.ForeignKey(Challenge, on_delete=models.CASCADE, related_name='candidates')

Then you can query the winner of each challenge with the highest like-count with a subquery like this:

from django.db.models import Count
from django.db.models import OuterRef, Subquery

cadidates = Candidates.objects.annotate(like_count=Count('likes')).filter(challange=OuterRef('pk')).order_by('-like_count')
queryset = Challenge.objects.annotate(winner=Subquery(cadidates.values('owner__username')[:1]))

This will give you a Challenge result query with an annotated winner like:

{'id': 1, 'title': 'Challange 1', 'winner': 'username'}