How to get the two best products for each type of product?

Question

Let's say I have two models that look 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 get two of the best products (two with the highest score) from each ProductType. Therefore, if I have Phones, Computers, TVs , like ProductTypes, I want the two best phones, computers, TVs.

Since I don’t even know how to do this in MySQL, I tried to find a MySQL solution, but the ones I find are extremely complicated, and it is not very difficult to do.

I tend to create my own model for the best products and have a cronjob to fix this, but I would like to see if there is an easier solution for this.

Answer 1

Well, you can make a method inside the ProductType class that returns the two best results for your 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 just do something like:

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

Answer 2

While adam's solution is correct, more django-ish will use a dispatcher.

See Class Managers and Methods on James Bennett's Blog

Other benefits include:

• the manager carries all the code depending on the specific request, avoiding the interference of the model class
• the same manager class can be shared between several classes
• "--" "m2m"

, :

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]

:

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

, , use_for_related_field, , top_score , .

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

: , top_score . , ProductType .

Answer 3

-

    product_type_list = ProductType.objects.value("id")
    for product_type in product_type_list:
        Product.objects.filter(
            product_type=product_type
        ).order_by("-score")[0:2]