Select and Prefetch to related

All we understood this way help us keep away N+1 hit to the db, 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

Ooppppps

Even you try

Tag.objects.select_related('book')

That's will give you the same error too.

For the select_related we can only use if the ForeignKey involves.

So next is interesting part

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)

Hee it's worked either one of them is not ManyToManyField. There is no sign of any error as select_related

But very very different statements as you can see and compare.

Wait what.. what is book_tag ? where do you come from?.

It's the table's name auto generated by django.

For the m2m relation we have to have a middle table to map both id's.

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's explained why we got unmatch statement.

We have to do more JOIN after get the tag's id, instead of go to destination as author does.

Interested part

But in the Tag's view. How do i get the books contains this tag?.

As we know the Tag 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 expected more than 24hrs for a single day.

Go to the docs as a 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`.

Just related_name at the Python object level, not relate to sql stuff. If you dont set, django will use it as 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's work 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...

More salt?

If this Tag table have others relation than Book like a post, news, event, etc...

Ok let's do it.

i added 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