The trick is to use <QuerySet>.query.group_by.
Here's an example that will work::
>>> from collection.models import Edition, User, Rating
>>> Rating.objects.all().delete()
>>> Edition.objects.all().delete()
>>>
>>> e1 = Edition.objects.create(title=u'Title1', binding=u'')
>>> e2 = Edition.objects.create(title=u'Title2', binding=u'')
>>> e3 = Edition.objects.create(title=u'Title3', binding=u'')
>>> me = User.objects.all()[0]
>>> __ = Rating.objects.create(user=me, edition=e1, rating=3)
>>> __ = Rating.objects.create(user=me, edition=e1, rating=4)
>>> __ = Rating.objects.create(user=me, edition=e1, rating=2)
>>> __ = Rating.objects.create(user=me, edition=e2, rating=5)
>>> __ = Rating.objects.create(user=me, edition=e2, rating=6)
>>> __ = Rating.objects.create(user=me, edition=e2, rating=5)
>>> __ = Rating.objects.create(user=me, edition=e3, rating=1)
>>> __ = Rating.objects.create(user=me, edition=e3, rating=2)
>>> __ = Rating.objects.create(user=me, edition=e3, rating=1)
>>> __ = Rating.objects.create(user=me, edition=e3, rating=3)
>>>
So at this point Edition 1 has 3 ratings of 3,4 and 2 (average = 3)
Edition 2 ratings 5, 6 and 5 (average = 5.33)
Edition 3 ratings 1, 2, 1, 3 (average = 1.75)
So the highest average is Edition 2.
Now we'll do the complex select.
>>> qs = Rating.objects.all().order_by('-avg')
>>> qs = qs.extra(select={'s':'sum(rating)',
... 'c':'count(rating)',
... 'avg':'sum(rating)::float/count(rating)'})
>>> qs = qs.values('edition_id','s','c','avg')
>>> qs.query.group_by = ['edition_id']
>>> print qs.query.as_sql()
(u'SELECT (sum(rating)) AS "s", (sum(rating)::float/count(rating)) AS
"avg", (count(rating)) AS "c", "collection_rating"."edition_id" FROM
"collection_rating" GROUP BY edition_id ORDER BY "avg" DESC', ())
>>> for e in qs:
... print e
...
{'s': 16L, 'avg': 5.3333333333333304, 'edition_id': 2, 'c': 3L}
{'s': 9L, 'avg': 3.0, 'edition_id': 1, 'c': 3L}
{'s': 7L, 'avg': 1.75, 'edition_id': 3, 'c': 4L}
>>> edition_ids = [x['edition_id'] for x in qs]
>>> edition_ids
[2, 1, 3]
That should get you going. Now you have to do something with this
ordered list of edition_ids.
You can use this for a filter on a more elaborate QuerySet. Something
like this:
cool_editions = Editions.objects.filter(id__in=edition_ids).exclude
(title__icontains='Fudge').order_by('-binding')
I hope that helps!
On Nov 28, 10:07 am, coan <[EMAIL PROTECTED]> wrote:
> I have book editions in my database. Users can rate editions. If they
> want, they can also add them to their book collection.
> Here is a simplified overview of the models:
>
> class Edition(models.Model):
> title = models.models.CharField(max_length=255)
> binding = models.models.CharField(max_length=30)
>
> class Bookcollection(models.Model):
> user = models.ForeignKey(User)
> edition = models.ForeignKey(Edition)
>
> class Rating(models.Model):
> user = models.ForeignKey(User)
> edition = models.ForeignKey(Edition)
> rating = models.PositiveIntegerField()
>
> I can fetch all the books in a users bookcollection with editions =
> user.edition_set.all()
> What do I do if I want to order a users bookcollection by the rating
> he or she gave it?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
