QuerySet進階 · django base

[參考文檔][1] ### 查看 Django queryset 執行的 SQL 在QuerySet最后增加query，即可查詢SQL ~~~ Author.objects.all().query ~~~ ### extra 實現別名原始名稱和別名都會顯示 ~~~ Tag.objects.all().extra(select={'tag_name': 'name'}).query.__str__() Out[47]: u'SELECT (name) AS "tag_name", "blog_tag"."id", "blog_tag"."name" FROM "blog_tag"' ~~~ 不顯示原始名稱，使用defer除去 ~~~ Tag.objects.all().extra(select={'tag_name': 'name'}).defer('name').query.__str__() Out[49]: u'SELECT (name) AS "tag_name", "blog_tag"."id" FROM "blog_tag"' ~~~ ### defer 排除不需要的字段在復雜的情況下，表中可能有些字段內容非常多，取出來轉化成 Python 對象會占用大量的資源。這時候可以用 defer 來排除這些字段，比如我們在文章列表頁，只需要文章的標題和作者，沒有必要把文章的內容也獲取出來（因為會轉換成python對象，浪費內存） ### only 僅選擇需要的字段和 defer 相反，only 用于取出需要的字段，假如我們只需要查出作者的名稱 ~~~ Author.objects.all().only('name') ~~~ >[info] only類似于value_list只獲取一個字段 ### select_related 優化一對一，多對一查詢查詢了兩次 ~~~ >>> art = Article.objects.filter(id=1).first() (0.000) SELECT "app_article"."id", "app_article"."title", "app_article"."author_id", "app_article"."content", "app_article"."score" FROM "app_article" WHERE "app_article"."id" = 1 ORDER BY "app_article"."id" ASC LIMIT 1; args=(1 ,) >>> art.title 'Django 教程_1' >>> art.author (0.000) SELECT "app_author"."id", "app_author"."name", "app_author"."qq", "app_author"."addr", "app_author"."email" FROM "app_author" WHERE "app_author"."id" = 2; args=(2,) <Author: twz915> >>> art.author.name 'twz915' ~~~ 查詢了一次 ~~~ >>> art = Article.objects.filter(id=1).select_related('author').first() (0.000) SELECT "app_article"."id", "app_article"."title", "app_article"."author_id", "app_article"."content", "app_article"."score", "app_author"."id", "app_author"."name", "app_author"."qq", "app_author"."addr", "app_author"."e mail" FROM "app_article" INNER JOIN "app_author" ON ("app_article"."author_id" = "app_author"."id") WHERE "app_article"."id" = 1 ORDER BY "app_article"."id" ASC LIMIT 1; args=(1,) >>> art.author <Author: twz915> >>> art.author.name 'twz915' ~~~ ### prefetch_related 優化一對多，多對多查詢和 select_related 功能類似，但是實現不同。 select_related 是使用 **SQL JOIN** 一次性取出相關的內容。 prefetch_related 用于一對多，多對多的情況，這時 select_related 用不了，因為當前一條有好幾條與之相關的內容。 prefetch_related是通過再執行一條額外的SQL語句，然后用 Python 把兩次SQL查詢的**內容關聯**（joining)到一起 >[info]使用和不使用的區別在第二步，會不會有查詢`art.tags.all()` 使用prefetch_related ~~~ >>> art = Article.objects.all().prefetch_related('tags').first() (0.000) SELECT "app_article"."id", "app_article"."title", "app_article"."author_id", "app_article"."content", "app_article"."score" FROM "app_article" ORDER BY "app_article"."id" ASC LIMIT 1; args=() (0.001) SELECT ("app_article_tags"."article_id") AS "_prefetch_related_val_article_id", "app_tag"."id", "app_tag"."name" FROM "app_tag" INNER JOIN "app_article_tags" ON ("app_tag"."id" = "app_article_tags"."tag_id") WHERE "app_a rticle_tags"."article_id" IN (1); args=(1,) >>> art.tags.all() <QuerySet [<Tag: Django>]> >>> art.tags.all().first().name (0.000) SELECT "app_tag"."id", "app_tag"."name" FROM "app_tag" INNER JOIN "app_article_tags" ON ("app_tag"."id" = "app_article_tags"."tag_id") WHERE "app_article_tags"."article_id" = 1 ORDER BY "app_tag"."id" ASC LIMIT 1; args=( 1,) 'Django' ~~~ 不使用prefetch_related ~~~ >>> art = Article.objects.all().first() (0.000) SELECT "app_article"."id", "app_article"."title", "app_article"."author_id", "app_article"."content", "app_article"."score" FROM "app_article" ORDER BY "app_article"."id" ASC LIMIT 1; args=() >>> art.tags.all() (0.000) SELECT "app_tag"."id", "app_tag"."name" FROM "app_tag" INNER JOIN "app_article_tags" ON ("app_tag"."id" = "app_article_tags"."tag_id") WHERE "app_article_tags"."article_id" = 1 LIMIT 21; args=(1,) <QuerySet [<Tag: Django>]> >>> art.tags.all().first().name (0.000) SELECT "app_tag"."id", "app_tag"."name" FROM "app_tag" INNER JOIN "app_article_tags" ON ("app_tag"."id" = "app_article_tags"."tag_id") WHERE "app_article_tags"."article_id" = 1 ORDER BY "app_tag"."id" ASC LIMIT 1; args=( 1,) 'Django' ~~~ [1]:http://code.ziqiangxuetang.com/django/django-queryset-advance.html