lundi 10 août 2015

Annotating with Avg works in sqlite but not postgresql

I have a model that looks like this:

class Review(models.Model):
    RATING_CHOICES = (
        ('1', 'One Star - I can\'t recommend it'),
        ('2', 'Two Star - It was okay'),
        ('3', 'Three Star - I liked it'),
        ('4', 'Four Star - I liked it!'),
        ('5', 'Five Star - I loved it! New favorite!'),
        )
    rating = models.CharField(max_length=10, choices=RATING_CHOICES, blank=False)
    media_object = models.ForeignKey(MediaObject)
    ...

A given MediaObject may have many Reviews, as you can see. I would like to get a queryset of all MediaObjects sorted by the average rating of each object's Reviews.

The following lookup works perfectly when using SQLite:

MediaObject.objects.all().annotate(ravg=Avg('review__rating')).order_by('-ravg')

However, on postgresql, that lookup returns the following error:

ProgrammingError: function avg(character varying) does not exist

I understand the error but do not understand how I can fix/work around it. How can I cast rating to a number in this case before trying to Avg it using the Django ORM?

Aucun commentaire:

Enregistrer un commentaire