How to do this join query in Django

Question

In Django, I have two models:

class Product(models.Model):
    name = models.CharField(max_length = 50)
    categories = models.ManyToManyField(Category)

class ProductRank(models.Model):
    product = models.ForeignKey(Product)
    rank = models.IntegerField(default = 0)

I put the rank into a separate table because every view of a page will cause the rank to change and I was worried that all these writes would make my other (mostly read) queries slow down.

I gather a list of Products from a simple query:

cat = Category.objects.get(pk = 1)
products = Product.objects.filter(categories = cat)

I would now like to get all the ranks for these products. I would prefer to do it all in one go (using a SQL join) and was wondering how to express that using Django's query mechanism.

What is the right way to do this in Django?

Answer 1

This can be done in Django, but you will need to restructure your models a little bit differently:

class Product(models.Model):
    name = models.CharField(max_length=50)
    product_rank = models.OneToOneField('ProductRank')

class ProductRank(models.Model):
    rank = models.IntegerField(default=0)

Now, when fetching Product objects, you can following the one-to-one relationship in one query using the select_related() method:

Product.objects.filter([...]).select_related()

This will produce one query that fetches product ranks using a join:

SELECT "example_product"."id", "example_product"."name", "example_product"."product_rank_id", "example_productrank"."id", "example_productrank"."rank" FROM "example_product" INNER JOIN "example_productrank" ON ("example_product"."product_rank_id" = "example_productrank"."id")

I had to move the relationship field between Product and ProductRank to the Product model because it looks like select_related() follows foreign keys in one direction only.

Answer 2

I haven't checked but:

products = Product.objects.filter(categories__pk=1).select_related()

Should grab every instance.