[PYTHON] Wai "Can Django do JOIN?"

(Note) This article is in Wai notation. With respect to Mr. Yametaro @Yametaro.

One day my home

Wai "I'm making a Django app as a hobby Nyaga" Wai "You can make a table just by writing a Model" Wai "Django is hungry to make a management screen too!"

DB normalized

Wai "Easy setting of foreign keys" Wai "For example, it is better to separate the table for player data and player results [^ 1]" [^ 1]: It looks like you don't need it if you look only here, but I'm using it elsewhere ...

Players

FielderTotalResults

• It is shown briefly for explanation.

Wai "Fielder Total Results'player_id is a foreign key" Wai "It corresponds to ʻid in the Players table"

Wai "In this example, FieldeTotalResults id 1 is Tanaka, 2 records are Suzuki's results." Wai "It's clear that 3 is Takahashi and 4 is Yamada."

I want to join INNER JOIN

Wai "The Fielder Total Results table is the result of totaling the results for each player." Wai "I want to combine the result with Players and display it on the screen."

Wai "It looks like this in the image" Wai "I wonder if I can write so that the ʻid of Players and the player_id` of Fielder Total Results match. " Wai "For example, if you want to combine Suzuki's player information and grades"

from django.db import models
from eikan.models import Players, FielderTotalResults

p = Players.objects.get(pk=2)
f = FielderTotalResults.objects.get(player_id=p.id)

Wai "Hmm? This is just getting the same player's record at each table!" Wai "I want to combine and pass it to Template as one data" Wai "Hmm, even if you look at the official document, there is no description of JOIN." Wai "In such a case, do you get it separately and do something with Template?"

Actually, it's already INNER JOIN

Daughter (** 2 years old **) [^ 2] "Papa! If you get a child table with Django" [^ 2]: It really exists Daughter "I can get it with the parent table and INNER JOIN already"

Wai "What !?" Wai "(At the age of two, I already understand Django better than Wai ...!?)"

Wai "Daughter, where did you know that?" Daughter "I remember playing with my friends at the nursery school"

Wai "(Can nursery play with Django ...?)" Wai "(I didn't know because Wai went to kindergarten ...)"

Wai "But my daughter, are you really connected to the parent table?" Wai "If you write f.name " Wai "Don't do that, you get angry."

Daughter "If you're a dad, you're a manual person" Daughter "In such a case, you first get the record of the child table, right?"

f = FielderTotalResults.objects.get(player_id=2)

Daughter "In this state, if you write child table.foreign key.column name of parent table, you can get the value. "

f.player_id.name
>>>Suzuki

Wai "Honestly! (Manual human ...?)"

Daughter "If you write in the same way with Template" Daughter "You can use the column values of the parent table without having to pass the parent table." Daughter "Parent-> Child-> If grandchildren have a relationship" Daughter "grandchild table. child foreign key. parent foreign key. parent element" Daughter "I can write"

Wai "Wow ... it's amazing ..." Wai "I can't join anywhere, but Django can easily join."

Daughter "Daddy is not referenced by anyone" Wai "Se, Sayana ..." Wai "(What a destructive 2-year-old child ...)"

** Below, added in the wake of comments **

Another day

Wai "Finally completed!" Wai "I was spared while I was sleeping, so I could only sleep for 8 hours!"

Yome (I'm sleeping enough, right?)

Wai "I'll give it to Heroku with a sample" Wai "Well, contents are contents I don't use it except for Wai!"

Daughter "I've done it" Daughter "I think this is a big deal" Daughter "This is education"

Wai "Fat !?" Wai (Under pressure like the former director of the Kansai baseball team ...!) Wai "Daughter, do you think that the scoring value is not so high for stolen bases in modern times?"

Daughter "Daddy, talk about Django, not Sabermetrics." Daughter "No matter how much you can get in the state of INNER JOIN" Daughter "Every time I try to use a value in the current writing style" Daughter "A lot of queries are issued and it gets very heavy" Daughter "The so-called N + 1 problem" Daughter "Actually, the one that Dad deployed on Heroku also has a lot of queries issued."

Wai "I thought it was heavy, but I wonder if it's like this ..." Wai "How did you know that my daughter was getting a lot of queries?"

Daughter "When I use django-debug-toolbar, I can see the queries that have been issued." Daughter "If you google, you'll know how to use it right away, so I'll omit it." Daughter "The query issued by Dad's app looks like this"

• Some will increase or decrease depending on the number of registered records.

Wai "Because the team details are obtained from various tables." Wai "It's really heavy" Wai "I'm sorry if this gets lighter"

Daughter "If you want to get and process records that are related in various places" Daughter "as it was a comment" Daughter "By using select_related (), you can stop issuing useless queries"

f = FielderTotalResults.objects.select_related('player_id').get(player_id=2)
f.player_id.name
>>>Suzuki

Daughter "It's like this as a way of writing" Daughter "By doing this, no matter how many times you use f.player_id.name " Daughter "The query is issued only the first time"

Wai "Wow ... it's amazing ..." Wai "Then I'll fix everything that applies for the time being"

n weeks later

Wai "It's getting lighter!" Wai "It's quite effective just to use select_related ()" Wai "Check with django-debug-toolbar" Wai "Easy to review the overall processing" Wai "This is the result of a major overhaul."

• Some will increase or decrease depending on the number of registered records.

Wai "I fixed it so that I wouldn't issue queries as much as possible." Wai "No matter where you display it, it's crispy!" Wai "It's obvious when you compare it!" Wai "And even if you add more data" Wai "The number of queries hasn't increased much." Wai "If you're curious, please actually touch it and compare it ~"

Heavy: https://trial-endb-v1.herokuapp.com/ Lighter: https://trial-endb-v2.herokuapp.com/

• Contains the same data

In the bath

Wai "Wai himself was impressed with this function, so I wrote this article." Wai "If you find this teratail post, you can read this article for a long time." Wai "I've talked about solving it soon" Wai "This article may be noisy for many people."

Daughter "Daddy" Daughter "Do you think an article that supplements teratail's answer will help someone?" Daughter "And by writing in Wai notation like this" Daughter "By re-experiencing success from failure" Daughter "It will take root in everyone's memory" Daughter "Actually, get a comment and try it" Daughter "Did you get it by comparing?"

Wai "Seyana" Wai "Wai himself may not feel the benefits even if he suddenly says the best practice," This is better! "" Wai "I hope someone reads this article and feels umami." Daughter "Ummmm!"

Wai "Can I eat ice cream when I get out of the bath?" Daughter "Wow, eat!"

Wai "(I've been toddling until this time)" Wai "(Tell me about the child who bears this)"

** ~ ~ (no punch line or yama) is over ~ ~ **

Github https://github.com/shimayu22/Eikan-Database

reference

Table joins that even SQL amateurs can understand (inner join and outer join) Summary of Django database operations When I checked the query generated by Django, it was issued in large numbers [Django] Ingenuity to reduce the number of queries (will be added at any time) Django Reverse Lookup Cheat Sheet (QuerySet) [Django 2.2] How do you display relations in Django? [List View] Search grandchildren in Django (https://qiita.com/yoko8ma/items/feb7776727911ddda8f1) How to find and eradicate N + 1 problems during development with Django