Python – How to do this join query in Django


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?

Best Solution

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:


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.