Select and Prefetch to related

We all understand this approach helps us avoid N+1 hits to the database, right?

We have simple models here.

class Author(models.Model):
	name = models.CharField(max_length=255)

	class Meta:
		db_table = "author"

class Tag(models.Model):
	name = models.CharField(max_length=255)

	class Meta:
		db_table = "tag"

class Book(models.Model):
	name = models.CharField(max_length=255)
	author = models.ForeignKey(Author, on_delete=models.CASCADE)
	tag = models.ManyToManyField(Tag)

	class Meta:
		db_table = "book"

Now let's do some shell

Book.objects.select_related("author")

SELECT "book"."id",
       "book"."name",
       "book"."author_id",
       "author"."id",
       "author"."name"
  FROM "book"
 INNER JOIN "author"
    ON ("book"."author_id" = "author"."id")
 LIMIT 21

Execution time: 0.000162s [Database: default]

Book.objects.select_related("tag")

FieldError: Invalid field name(s) given in select_related: 'tag'. Choices are: author

Oops!

Even if you try

Tag.objects.select_related('book')

That will give you the same error too.

For select_related, we can only use it if a ForeignKey is involved.

So the next part is interesting

In [21]: Book.objects.prefetch_related("author")
Out[21]: 
SELECT "book"."id",
       "book"."name",
       "book"."author_id"
  FROM "book"
 LIMIT 21

Execution time: 0.000097s [Database: default]
SELECT "author"."id",
       "author"."name"
  FROM "author"
 WHERE "author"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)

And now for the tag

In [23]: Book.objects.prefetch_related("tag")
Out[23]: 
SELECT "book"."id",
       "book"."name",
       "book"."author_id"
  FROM "book"
 LIMIT 21

Execution time: 0.000102s [Database: default]
SELECT ("book_tag"."book_id") AS "_prefetch_related_val_book_id",
       "tag"."id",
       "tag"."name"
  FROM "tag"
 INNER JOIN "book_tag"
    ON ("tag"."id" = "book_tag"."tag_id")
 WHERE "book_tag"."book_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)

Hey, it worked even though one of them is not ManyToManyField. There is no sign of any error like with select_related.

But the statements are very, very different as you can see and compare.

Wait, what... what is book_tag? Where do you come from?

It's the table name auto-generated by Django.

For the m2m relation, we have to have a middle table to map both IDs.

We can show the table in our db.

sqlite> 
SELECT 
    name
FROM 
    sqlite_schema
WHERE 
    type ='table' AND 
    name NOT LIKE 'django_%' AND 
    name NOT LIKE 'auth_%' AND 
    name NOT LIKE 'sqlite_%';
tag
book
book_tag
 
SELECT * FROM book_tag;

id  book_id  tag_id
--  -------  ------
1   1        2     
2   1        1     
4   1        3     
...

That explains why we got unmatched statements.

We have to do more JOINs after getting the tag's ID, instead of going directly to the destination as author does.

Interesting part

But from the Tag's perspective, how do I get the books that contain this tag?

As we know, the Tag is connected to Book through book_tag, right?

Tag.objects.filter().prefetch_related('book')

AttributeError: Cannot find 'book' on Tag object, 'book' is an invalid parameter to prefetch_related()

Argggggggggggggggg that's why I wish there were more than 24 hours in a single day.

Go to the docs as usual? No!

In [35]: vars(Tag)
Out[35]: 
mappingproxy({'__module__': 'author.models',
              '__doc__': 'Tag(id, name)',
              '_meta': <Options for Tag>,
              'DoesNotExist': author.models.Tag.DoesNotExist,
              'MultipleObjectsReturned': author.models.Tag.MultipleObjectsReturned,
              'name': <django.db.models.query_utils.DeferredAttribute at 0x7ffa7c952260>,
              'id': <django.db.models.query_utils.DeferredAttribute at 0x7ffa7c952380>,
              'objects': <django.db.models.manager.ManagerDescriptor at 0x7ffa7c952320>,
              'book_set': <django.db.models.fields.related_descriptors.ManyToManyDescriptor at 0x7ffa7c953610>})

Did you see that book_set just apply it.

In [36]: Tag.objects.filter().prefetch_related('book_set')
Out[36]: SELECT "tag"."id",
       "tag"."name"
  FROM "tag"
 LIMIT 21

Execution time: 0.000046s [Database: default]
SELECT ("book_tag"."tag_id") AS "_prefetch_related_val_tag_id",
       "book"."id",
       "book"."name",
       "book"."author_id"
  FROM "book"
 INNER JOIN "book_tag"
    ON ("book"."id" = "book_tag"."book_id")
 WHERE "book_tag"."tag_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)

Now the hack is `_set`.

It's just related_name at the Python object level, not related to SQL stuff. If you don't set it, Django will use it as the default.

Now I added it.

class Book(models.Model):
	name = models.CharField(max_length=255)
	author = models.ForeignKey(Author, on_delete=models.CASCADE)
	tag = models.ManyToManyField(Tag, related_name='book')

And check it again.

In [3]: vars(Tag)
Out[3]: 
mappingproxy({...
              'name': <django.db.models.query_utils.DeferredAttribute at 0x7fccd3436c20>,
              'id': <django.db.models.query_utils.DeferredAttribute at 0x7fccd3436d40>,
              'objects': <django.db.models.manager.ManagerDescriptor at 0x7fccd3436ce0>,
              'book': <django.db.models.fields.related_descriptors.ManyToManyDescriptor at 0x7fccd3436860>})

Tag.objects.filter().prefetch_related('book')
<QuerySet [...]>

Hooray, it works as I expected!

Now let's try with ForeignKey at the Author view

Author.objects.filter().prefetch_related('book_set')
<QuerySet [...]>

Works like a champ...

Want more?

If this Tag table has other relations besides Book, like post, news, event, etc.

OK, let's do it.

I added a new model below.

class Post(models.Model):
	name = models.CharField(max_length=255)
	content = models.TextField()
	tag = models.ManyToManyField(Tag)

	class Meta:
		db_table = "post"

Then run make/migrate.

In [7]: vars(Tag)
Out[7]: 
mappingproxy({...
              'objects': <django.db.models.manager.ManagerDescriptor at 0x7fccd3436ce0>,
              'book': <django.db.models.fields.related_descriptors.ManyToManyDescriptor at 0x7fccd3436860>,
              'post_set': <django.db.models.fields.related_descriptors.ManyToManyDescriptor at 0x7fccd3435d80>
})

Post.objects.filter().prefetch_related('book_set')
<QuerySet []>

Here it is!

dev-note