Django Query to get max rating

[Yadnesh Patil]

My model structure is like this:

class Group(models.Model):
    u_id                = models.ForeignKey(User)
    group_name          = models.CharField(max_length=50,unique=True)
    def __str__(self):              # __unicode__ on Python 2
        return self.group_name + " " + self.org_nam

class Members(models.Model):
    group_id             = models.ForeignKey(Group)
    name                 = models.CharField(max_length=60)
    def __str__(self):              # __unicode__ on Python 2
        return self.name + " " + self.gender

class Ratings(models.Model):
    member_id            = models.ForeignKey(Members)
    question             = models.ForeignKey(Questions) 
    rating               = models.DecimalField(max_digits=20,decimal_places=2,null=True,blank=True)

class GroupQn(models.Model):
    group_id             = models.ForeignKey(Group)
    qn_id                = models.ForeignKey(Questions)     

I want calculate maximum rating member for each question of a particular group:

What I have tried:

def top_ratings(group_id):
    group               = get_object_or_404(Group, pk=group_id)
    members             = Members.objects.filter(group_id=group)
    questionset         = GroupQn.objects.filter(group_id=group)
    top_rated_list  = []
    for i in questionset:
       max_rating = Ratings.objects.filter(question=i.qn_id,member_id=members).aggregate(Max('rating'))['rating__max']
       top_rated_list =  Ratings.objects.get(rating = max_rating)
    return top_rated_list

i am getting an error:

"MultipleObjectsReturned at /group/2 get() returned more than one Ratings -- it returned 2! Request Method: GET Request URL: 127.0.0.1:8000/group/2 Django Version: 1.7.5 Exception Type: MultipleObjectsReturned Exception Value: get() returned more than one Ratings -- it returned 2! – "

[Filipe Ximenes]

From what I could understand, you are trying to fetch all the top ratings for each question in a group, correct?

The problem is that this query:

top_rated_list = Ratings.objects.get(rating = max_rating)

​

is returning more than one rating instance. "get" queries are supposed to return a single result, if you need more than one, you should be using a "filter" query.

This is happening because there are more than one rating with a max_rating in your database. What i think you really meant to be doing is to fetch the best for rating for a single question, so your query would look like this:

top_rated_list = Ratings.objects.get(rating = max_rating, question=i)

​

But, this can also lead to the same error when two users give a question the same rate.

In this case, if it doesn't mater if you pick one or the other, a fix for this can be:

top_rated_list = Ratings.objects.filter(rating = max_rating, question=i).first()

​

Now, the way you are doing this, the "top_rated_list" will only have one rating object everytime. To return a list of ratings, you should appending to the list:

question_top_rate =  Ratings.objects.filter(rating = max_rating, question=i).first()
top_rated_list.append(question_top_rate)

​

Also, the way you are modelling the relationships, there is a many to many relationship from a Group to a Question, so a question belongs to multiple groups. If this is the behaviour you are looking for, you should be using a ManyToManyField as documented here:

https://docs.djangoproject.com/en/1.7/ref/models/fields/#manytomanyfield

also, take a look in the "through" parameter:

https://docs.djangoproject.com/en/1.7/ref/models/fields/#django.db.models.ManyToManyField.through

It what your really wanted was to a question belong to a single group, it would make more sense to have a ForeignKey to Group in the Question model.

You can also optimize the way you are doing the queries, but that's another topic.

--

Filipe Ximenes +55 (81) 8245-9204 Vinta Software Studio http://www.vinta.com.br