[PYTHON] When I checked the query generated by Django, it was issued in large numbers

Django:1.10

When you generate a query in Django, you'll end up with a lot of queries if you don't write it carefully.

Query log output

First, write the log settings in manage.py to output the query to the server console.

manage.py

LOGGING = {
    'version': 1,
    'formatters': {
        'all': {
            'format': '%(message)s'
        },
    },
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'all',
        },
    },
    'loggers': {
        'django': {
           'handlers': ['console'],
           'level': 'DEBUG',
        },
    },
}

Check the query

Let's execute a query to get the list of books published by the selected author that was used last time.

How to generate a query using the IN operator in Django http://qiita.com/nakkun/items/86a94e65fe6785325f54

Source

books = Book.objects.filter(author_id__in = form.cleaned_data['author'])

Execution result

(0.002) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)

The SQL was running as expected. I got the same query using "author_ \ _ id_ \ in" as shown below. (The number of "" is different)

Source

books = Book.objects.filter(author__id__in = form.cleaned_data['author'])

In the case of "author_ \ _ id_ \ _in", I was wondering if they would join, but that was not the case.

A large number of queries! ??

There is no particular problem so far, but if you try to display the author name after that, the execution result will be as follows.

Source

books = Book.objects.filter(author_id__in = form.cleaned_data['author'])
[print(book.author.name) for book in books]

Execution result

(0.003) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 1; args=(1,)
Hiroshi Mori
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani
(0.000) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" = 3; args=(3,)
Masamitsu Miyagitani

I have issued a fairly useless query.

So you need to write a source that will issue a joined query like this:

Source

books = Book.objects.select_related().filter(author_id__in = form.cleaned_data['author'])
[print(book.author.name) for book in books]

Execution result

(0.004) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id", "books_author"."id", "books_author"."name" FROM "books_book" INNER JOIN "books_author" ON ("books_book"."author_id" = "books_author"."id") WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

Now you only have to issue the SQL once.

It may be better to develop while checking SQL before you get used to it so that you do not throw a large amount of SQL without knowing it.

Supplement

About 3 other methods.

① How to use prefetch_related

Source

books = Book.objects.filter(author_id__in = form.cleaned_data['author']).prefetch_related('author')
[print(book.author.name) for book in books]

Execution result

(0.001) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

② Starting from the Author model

Source

authors = Author.objects.filter(id__in = form.cleaned_data['author'])
[[print(book.atuhor.name) for book in author.book_set.all()] for author in authors]

Execution result

(0.002) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
(0.001) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" = 1; args=(1,)
Hiroshi Mori
(0.000) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" = 3; args=(3,)
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani

③ Use prefetch_related starting from the Author model

Source

authors = Author.objects.filter(id__in = form.cleaned_data['author']).prefetch_related("book_set")
[[print(book.atuhor.name) for book in author.book_set.all()] for author in authors]

Execution result

(0.002) SELECT "books_author"."id", "books_author"."name" FROM "books_author" WHERE "books_author"."id" IN (1, 3); args=(1, 3)
(0.002) SELECT "books_book"."id", "books_book"."name", "books_book"."author_id" FROM "books_book" WHERE "books_book"."author_id" IN (1, 3); args=(1, 3)
Hiroshi Mori
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani
Masamitsu Miyagitani