Django snippets

class ItemManager(models.Manager):
    def top_rated(self):
        subquery = """(SELECT app_rating.item_id, AVG(app_rating.value) AS value 
                       FROM app_rating 
                       GROUP BY app_rating.item_id) AS item_rating_avg"""
        condition = 'item_rating_avg.item_id = app_item.id' # Join
        order = '-item_rating_avg.value'
        return self.get_query_set().extra(tables=[subquery],
                                          where=[condition]).order_by(order)
    def most_discussed(self):
        subquery = """(SELECT app_comment.item_id, SUM(1) AS value 
                       FROM app_comment 
                       GROUP BY app_comment.item_id) AS item_comment_count"""
        condition = 'item_comment_count.item_id = app_item.id' # Join
        order = '-item_comment_count.value'
        return self.get_query_set().extra(tables=[subquery],
                                          where=[condition]).order_by(order)

class Item(models.Model):
    ...   
    objects = ItemManager()
    ...

class Comment(models.Model):
    item = models.ForeignKey(Item)
    text = models.TextField()
    user = models.ForeignKey(User)

class Rating(models.Model):
    item = models.ForeignKey(Item)
    value = models.IntegerField()
    user = models.ForeignKey(User)