How to fetch the top two products for each product type?

Question

Let's say I have two models looking like this:

class ProductType(models.Model):
    product_type = models.CharField(max_length=100)

class Product(models.Model):
    name = models.CharField(max_length=200)
    slug = models.SlugField()

    product_type = models.ForeignKey(ProductType)

    score = models.PositiveIntegerField(default=0)

Now I want to fetch the top two products (the two with highest score) from each ProductType. So if I have Phones, Computers, TVs as ProductTypes I want the two best Phones, Computers, TVs.

As I don't even know how to do this in MySQL I've tried searching for the MySQL-solution but the ones I find is extremely complex and this doesn't feel like an extremely complex thing to do.

I am leaning towards making an own model for the top products and have a cronjob to fix this, but I'd like to see if there's an easier solution to this.

Answer 1

Well, you could make a method inside of the ProductType class that returns the top two results for its products:

class ProductType(models.Model):
    product_type = models.CharField(max_length=100)

    def TopTwoProducts(self):
        return self.product_set.order_by('-score')[0:2]

Then you would just do something like:

for type in ProductType.objects.all():
    type.TopTwoProducts()

Answer 2

While adam's solution is correct, a more django-ish way would be to use a manager.

See Managers versus class methods on James Bennett's blog

Among other advantages :

• a manager carries all query-specific code, while avoiding to clutter the model class
• the same manager class can be shared among several classes
• the manager can be used directly on a model class, or via a one-to-many or m2m relation

Thus, for the above question :

from django.db import models
from django.db.models.manager import Manager

class ProductScoreManager(Manager):

    use_for_related_field = True

    def top_score(self, limit=2):
        return self.get_query_set().order_by('-score')[:limit]

Then add this manager class as default manager for Product :

class Product(models.Model):
    ...
    objects = ProductScoreManager()
    ...

Now, thanks to objects overriding the default manager, and use_for_related_field allowing its use in relation queries, the top_score method can be used in any model related to products.

myproducttype = ProductType.objects.get(...)
myproducttype.products.top_score() # return top 2 products

This allows a more consistent syntax : the related products is always accessed via products, top_score acting as a filter. Additionally, ProductType class is no more cluttered with Product's query logic.