python django django-models django-queryset django-annotate

Django - annotate the most frequent field taken from another model linked with foreignKey

I have a model of users and a model with a survey in which users express their opinion with a vote with an integer number that identified a particular color of eyes. A srcID user describes the color of the eyes of a dstID user according to his opinion. A user can votes himself. It's possible that a user doesn't receive any votes so there isn't a tuple with dstID equal to his ID. The integer number of eyeColor rapresent a specific color, for instance:

1 => blue eyes
2 => lightblue eyes
3 => brown eyes
4 => green eyes

ecc.

 class user:
      userID = models.AutoField(primary_key = True, auto_created = True, unique = True)
      name = models.CharField(max_length=100)

  class survey:
      srcID = models.ForeignKey(user, on_delete = models.CASCADE, related_name='hookSrc')
      dstID = models.ForeignKey(user, on_delete = models.CASCADE, related_name='hookDst')
      eyesColor= models.IntegerField()

My goal is to annotate in the user model the most frequent type of eyeColor voted, so the eyesColor that other people think is the eyesColor of that user. If a user doesn't receive any votes I want to annotate 0. In case there are more than one color with the same voting frequency, if the user has voted himself (tuple with srcID = dstID) and his vote is assigned to one of the most voted eyesColor then this has priority and will be selected. The query that I want is somenthing similar to what I have shown below:

value = user.objects.annotate(eyesColorMostFreq = ?)

I want to annotate eyesColorMostFreq to user model in order to manipulate it later. How can I do that?

Solution

This should yield the most frequent eyes color for each user.

subquery = Subquery(survey.objects.filter(srcID=OuterRef('pk')).values('eyesColor').alias(votes=Count('eyesColor')).order_by('-votes')[1:])
user.objects.alias(eyesColorMostVoted=subquery).annotate(eyesColorMostFreq=Case(When(eyesColorMostVoted__isnull=True, then=0), default=F('eyesColorMostVoted')))

This is already an heavy query so i do not it is a good idea to pick the color voted by the user itslef when multiple colors have the same number of vote. Tell me if it is essential, in that case i'll add it

EDIT:

subquery = Subquery(survey.objects.filter(srcID=OuterRef('pk')).alias(is_self_vote=Case(When(destID=OuterRef('pk'), then=1), default=0), votes=Count('eyesColor')).order_by('-votes', '-is_self_vote').values('eyesColor')[1:])
user.objects.alias(eyesColorMostVoted=subquery).annotate(eyesColorMostFreq=Case(When(eyesColorMostVoted__isnull=True, then=0), default=F('eyesColorMostVoted')))