Django proper use of select_related or prefetch_related on a ForeignKey

Question

I'm trying to figure out how to use select_related or prefetch_related to optimize a query from the other end of a foreign key. For example:

Say I have some models such as the following:

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

class ProductImage(models.Model):
    image = models.ImageField(upload_to="images")
    product = models.ForeignKey("myapp.Product", related_name="product_images")

If I want to print all of the ProductImage, I have to iterate through all of the products and then, for each product, iterate through all of the product images. However, this produces O(n * m) database queries.

for product in Product.objects.all():
    print product.product_images.all()

I'm wondering if there is a way to utilize select_related or prefetch_related to reduce this lookup to one or two queries.

As documented by Django, I can get select_related to work when I select ProductImage. As you can see, adding the select_related creates a JOIN to the product table.

>>> ProductImage.objects.all().query.sql_with_params()
(u'SELECT "myapp_productimage"."id",  ...  FROM "myapp_productimage" .... 

>>> ProductImage.objects.select_related('product').query.sql_with_params()
(u'SELECT "myapp_productimage"."id", ...   FROM "myapp_productimage" LEFT OUTER JOIN ....

However, how do I accomplish the reverse? For example, how do I query all of the Products and have it JOIN on the ProductImages? Doing something like the following doesn't seem to work.

Product.objects.select_related('product_images').query.sql_with_params()

Is something like this possible in Django?

Thanks for your consideration.

Lonni

Answer 1

That is exactly what prefetch_related does.

Product.objects.prefetch_related('product_images')

However, there's no point in using query.sql_with_params() to diagnose this: prefetch_related does two queries, and the second one won't show up there. You should use django.db.connection.queries to examine the queries that Django is making, or even better use the Django debug toolbar to show you.

Supinator

Thank you very much Daniel. Also, good to know that the sql_with_params() isn't showing the full picture. I'll try using the other methods that you suggest. – Lonni

Answer 2

select_related is used when you have a forward foreign-key relationship and prefetch_related should be used when there is a reverse foreign-key relationship.

Baking

Answer 3

[Quick Answer]

Just in more detail:

• Use select_related when you have a *-to-one relationship — OneToOneField and Foreignkey which is known as many-to-one

• Use prefetch_related when you have a *-to-many relationship — ManyToManyField and reverse Foreignkey which is known as one-to-many

---

[NOTE]:

• Do not forget to set related_name= parameter which is helpful in the reverse queries:

  Product.objects.prefetch_related('product_images')

• Otherwise, you have to use it as follows:

  Product.objects.prefetch_related('productimage_set')

Rhatany