DjangoでDB操作するときの備忘録です（SQLの知識前提です）。
テーブルを構成するデータモデルからORMを用いてデータを取得するものだ、SQLを使用するのは奥の手とよく言われますが、だからといってSQLの知識をおざなりにしてはいけません。
なぜORMなのか
ORM（Objected-Ralational Mapping）のメリットは保守性が高いと挙げられていますが、もっと大きな理由はSQLインジェクションといった外部からの不正アクセス攻撃に強いからです。ではそのORMは、内部でどうやってDBテーブルから値を取得しているのでしょうか。そして、ORMとSQLの違いは何でしょうか？
その疑問を簡単に解き明かす便利なプロパティがあります。
queryプロパティ
処理を施したオブジェクトに対し、queryプロパティを付与するとSQLが表示されます。つまり、なんてことはなく、ORMでやっていることは、各プロパティの命令にしたがってSQL文を作っているだけです。
queryの使い方
result = Hoge.objects.all()
print(result.query)
このように記述すれば、テーブルスキーマを含めた丁寧なSQL文が返されます。
したがってORMのプロパティを闇雲に覚えるのではなく、SQLのそれぞれの関数や句に対し、何を使うべきなのかをまとめていくともっと効率よく機能を覚えることができるはずです。
ですが、その相関をうまくまとめた、記事がなかったので、この機会に作ってみました。ちなみに実例の上行がORMでの記述、下行がSQLで記述する場合となります（queryプロパティで展開した値ではない）。
From句とselect句
まずfrom句（使用するテーブルを選択する句）にあたるものはデータモデルからオブジェクトを取得するobjectsプロパティがそれに当たります。
これをORMで記述すると以下のようになります。
MstPublisher.objects
# from MstPublisher;
select句
対する取得カラムを決定させるselect句ですが、*のように全表示したい場合はallを使用し、取得カラムを限定したい場合はvaluesを使用します。ちなみに前述したようにall()を省略すると、全カラム選択となります。
全選択(*)
objectsの後に何か条件を付する別のプロパティを付与した場合はallは不要です。
例：出版社マスタから全カラムを取得する
MstPublisher.objects.all()
# select * from MstPublisher; //実際は全カラムがテーブルスキーマを含め表示される
カラムを限定する
カラムを限定する場合はvalues（日本語で「値」のこと）を指定し、単一ならばvalues('hoge')とします。別名でも使用できます。
例：出版社マスタ（テーブル名MstPublisher）から名前（カラム名:name）を取得する
MstPublisher.objects.values('name')
# select name from MstPublisher;
※複数のカラムを取得する場合は以下のように記述します
MstPulbisher.objects.values('id','name')
# select id,name from MstPublisher;
集計関数を使用する
集計関数とはcount(件数)、sum（合計）、max（最大値）、min（最小値）、avg（平均値）などで数値に対して使用します。この集計作業を行うプロパティはaggregate関数であり、共通して以下のように記述します。
※aggregateは集計、総計を意味する単語です。
aggregate(カラム別名=任意の関数('対象となるカラム'))
また、いずれも関数を使用する場合はmodelsから予約関数をインポートする必要があります。また引数は変数ではなく、カラム名を明示的に記述する必要があります。
用意されている関数にはCount(件数)、Max(最大値)、Min(最小値)、Avg(平均値)などがあります。
※例：書籍トランザクション（TrnBooks）における価格(price)平均値を求める
from django.db.models import Avg #予約関数（Aveはaverage…平均のこと）
TrnBooks.objects.aggregate(average=Avg('price'))
# select avg(price) as average from TrnBooks;
集約関数に対し、取得カラムを限定する
取得カラムを限定したい場合は、aggregateを使用できません。ですがannotateを用いるとvaluesにカラムを引数指定できます（annotateは直訳すれば注釈という意味ですが、コンピュータ用語ではファイルなどを添付するような意味もあるみたいです）。したがって、aggregateがあくまで全体の集計結果だけを導くのに対し、annotateはそれぞれの計算結果をオブジェクトにして返すので、取得カラムを任意に指定することが可能となります。
from django.db.models import Avg
TrnBooks.objects.annotate(average=Avg('price')).values('average')
# select avg(price) as average from TrnBooks;
直接、値だけを取得したい場合
aggregateを用いたデータモデルに対しget('カラム名')と指定するだけです（getはORMに用意されたプロパティではなくて、明示的にプロパティを取得するモデルの予約関数）。たとえば、前述の平均値ならば
TrnBooks.objects.aggregate(average=Avg('price')).get('average')
とすれば計算結果だけを取得できます。結果をExcelなどに直接代入したい場合、非常に便利です。
distinct
distinct句は重複する値を集約するための句です。たとえば、書籍を持つ出版社からプルダウンリストを作成したい場合、重複する出版社を集約する必要があります。そんな場合は、distinctを用います。
※distinctとは「個別の」という形容詞です。形容詞というのがヒントで、必ず名詞の前に付与する必要があります。したがってSQLでも、distinctの後には必ず全カラムを示す*や個別のカラム名を表記する必要があります。
ただ、ORMはこのdistinctをメソッド化しています。
例：書籍トランザクションから出版社リストを作成する（書籍トランザクションに存在する出版社だけが取得される）
TrnBooks.objects.values('publisher').distinct()
# select distinct publisher as average from TrnBooks;
count distinct
count関数は件数を取得する関数ですが、これはよくdistinctと併用します。こうすることで、重複を除いた件数を取得できます。その方法はCount関数の引数にフラグを追加します。
例：書籍トランザクションに記載されている出版社の件数を求める
from Django.models import Count
TrnBooks.objects.aggregate(cnt=Count(publisher,distinct=True))
# select count(distinct publisher) as average from TrnBooks;
※annotateを用いる場合はdistinctプロパティを併記し、カラムを集約します。
rom Django.models import Count
TrnBooks.objects.annotate(cnt=Count(publisher)).values('cnt').distinct()
# select count(distinct publisher) as average from TrnBooks;
where句
where句は条件指定のための句です。つまりデータをどこから持ってくるかを指定します。それをORMで示すとfilterが該当します。またfilterの引数には条件文を代入でき、たとえば完全一致の場合はSQLと同じ=となります。また、filter句を付与した場合、標準ではall句を省略した形と同じになります。
例：書籍トランザクションから小学館の書籍を取得する
TrnBooks.objects.filter(publisher = '小学館')
# select * from TrnBooks where publisher = '小学館';
=とisの違い。なぜnullはisなのか？
これを説明するにはまずisについて知っておく必要があります。よくあるA is Bという文はSVCの文法に即してAがBであるという同格と説明されることが多いですが、厳密にはAがBという状態であるという意味を持っています。これをSQL文に当てはめ、もしも検索条件が`published is NULL`の場合、publisherはNULLという状態であることを示しています。対してA = Bというのは、ANSIによって規格化されており BをAに代入しても等位の関係を保てる、すなわちSQLでは真である という関係を意味しています。したがってpublisher = '小学館'は、publisherが小学館という条件が真である という意味合いを持っています。
ではpublisher = NULL に真だと何がまずいのかということですが、nullというのは値が存在していないという状態です。なので比較対象が異なり、NULLかそうでないかの判断しかしていません。
また、これはなぜ副問合せに=を使用するとエラーが発生することがあるのか、という部分に大きく関わってくる要素です。
like構文
like句は部分一致のための予約語で、前方一致はlike '%hoge'、中間一致はlike '%hoge%'、そして後方一致は like '%hoge' となります。これがORMの場合は以下のようにメタ構文を付記します。
前方一致（～で始まる）
like '%hoge'のような前方一致で条件を付与したい場合はstart_withを用います。
例：出版社マスタから新で始まる出版社を取得する。
MstPublisher.objects.filter(publisher__startwith = '新')
# select * from MstPublisher where publisher like '新%'; 新潮社、新学社などが表示される
双方一致（～を含む）
**like '%hoge%'**のような双方一致の場合は、containsを用います。containとは含むという意味です。
例：書籍トランザクションから、のを含む書籍名を取得する
TrnBooks.objects.filter(publisher__contains = 'の').values('name')
# select name from TrnBooks where publisher like '%の%';
後方一致（～で終わる）
like 'hoge%'のような後方一致で条件を付与したい場合はend_withを用います。
例：出版社マスタから社で終わる出版社を取得する
MstPublisher.objects.filter(publisher__endwith = '社')
# select * from MstPublisher where publisher like '%社'; 講談社、新潮社、集英社などが表示される
※多分大丈夫だと思いますが、likeは「～のような」という近似を示す副詞です（好きのlikeは語源が違います）。
数値を指定する
SQLでは数値に対してwhere句で >（超）、>=（以上）、<（未満）,<=（以下）といった不等号指定と、あとbetweenという範囲指定ができます。これをORMで再現する場合は、同様にメタ関数を利用します。
超
超とはその値を超えるものです。gtと表示しますが、これは英語のgreater than（それより大きい）の略です。ここで注意点、thanは基準値を示す副詞ですが、その基準値を含まない ことに注意してください。
例：5000円超の書籍を取得する（5000円のものは含まない）
TrnBooks.objects.filter(price__gt = 5000)
# select name from TrnBooks where price > 5000;
以上
以上はgteと表示しますが、これは英語のgreater than and equal（それより大きい及び同一）の略です。
例：5000円以上の書籍を取得する（5000円のものは含む）
TrnBooks.objects.filter(price__gte = 5000).values('name')
# select name from TrnBooks where price >= 5000;
未満
未満はltと表示しますが、これは英語のless than（それより小さい）の略です。
例：1000円未満の書籍を取得する（1000円のものは含まれない）
TrnBooks.objects.filter(price__lt = 1000).values('name')
# select name from TrnBooks where price < 1000;
以下
未満はlteと表示しますが、これは英語のless than and equal（それより小さい及び同一）の略です。
例：1000円以下の書籍を取得する（1000円のものは含まれる）
TrnBooks.objects.filter(price__lte = 1000).values('name')
# select name from TrnBooks where price <= 1000;
範囲指定
範囲指定はbetweenを用い、必ず●●以上●●以下の指定となります。ORMではrangeを用います。
なおbetweenは（※基本）2つの、確定している対象間を意味する前置詞です。
※厳密には3つ以上でも使う場合があります。ただ、そこを追及すると英語の授業になってしまうので。
例：3000円以上4000円以下の書籍を取得する
TrnBooks.objects.filter(price__range = (2000,3000)).values('name')
# select name from TrnBooks where price between(2000 and 3000);
複合条件
つまり、orやandを用いる場合です。
and
andはそのまま、filterに対する引数をn+1で増やしていくだけです。
例：書籍トランザクションから価格が3000円以上、かつ出版社が学研の書籍を取得する
TrnBooks.objects.filter(price__gte = 3000,publisher='学研').values("name")
#select name from TrnBooks where price >= 3000 and publisher = '学研'
or
orはQオブジェクトを取得し、それの引数に条件式を代入します。
例：書籍トランザクションから出版社が学研またはGakkenを含む書籍を取得する
from Django.models import Q
TrnBooks.objects.filter(Q(publisher__contains = '学研')|Q(publisher__contains='Gakken')).values("name")
#select name from TrnBooks where price >= 3000 or publisher = '学研'
※ちなみに対象カラムが同一、かつ等価条件ならばor句の代わりに次に挙げるin句も利用できます。
in句
inは条件指定の句であり、SQLでは同じカラムに対しor条件を付与したり、副問合せ（別のテーブルから該当する値を問い合わせること）に用いたりします。これはORMでは同様にfilterを用い、メタ関数はinを用います。
※このin句が便利なのは、対象が同じならば複数のor条件に対しても簡単に記述できることです。また、このinは後述する副問合せでも頻用します。
例：ジャンルが文庫または新書の書籍を取得する。
TrnBooks.objects.filter(genre__in = ['文庫','新書']).values('name')
#select name from TrnBooks where genre in ('文庫','新書')
副問合せをする
※副問合せはサブクエリとも言います。この副問合せは非常によく使用します。
副問合せもこのinなどを用います。たとえば出版社マスタにrun_flg（経営状況）というカラムがあり、それがTrueの書籍を取得するとします。ここでは出版社というマスタから現存企業のみを検索条件としています。なので、その現存出版社はTrnBooks上から複数ヒットするはずです。なので条件を=にしてしまうとまずいことになるのはわかるでしょう（複数ヒットした時点でエラーとなりますが、これは先ほど補足で説明した=の意味どおりで、真偽関係が成立しなくなるからです）。
ただ、この副問合せは、入れ子構造にする理屈さえわかってしまえば何も難しくはないです。ORMでの書き方も何も難しく考えることなく、基本文を入れ子構造にしているだけです。
例：書籍トランザクションから現存出版社の書籍を取得する
TrnBooks.objects.filter(publisher__in = MstPublisher.objects.values('name').filter(run_flg = True)).values('name')
#select name from TrnBooks where publisher in (select name from MstBooks where run_flg = True)
exists
副問合せはexistsという条件絞り込みも利用可能(Django3からはExistsという関数が使用できる)ですが、inと比較するとパフォーマンスが落ちるのであまり自分は使用しないです。ですが条件を絞り込んでいきたい(引き算にする)場合は有効かも知れません。またexistsは否定で用いることが多いのですが、その場合は接頭辞にチルダ（~）を付与します。ちなみに、OuterRefはfilter関数内部において、新たに外部キー指定するための関数です。
例：書籍トランザクションに記載のない出版社を取得する(TrnBooksにおける出版社はpub_codeというコードで記載されている)
from django.db.models import Exists,OuterRef
MstPublisher.objects.filter(~Exists(TrnBooks.objects.filter(pub_code != OuterRef('code')))).values(id,name)
#select id,name from MstBooks where not exists(select 1 as a from TrnBooks where pub_code = MstBooks.code)
group by
group by句によるグループ化はvaluesで行います。values(*[hoge])とすることでグループ化が可能になります。この場合はaggregateを使用できませんのでannotateを使用します。またgroup by句のルールで、select句で取得するカラムはgroup by句にも表記する必要があるので、以下のように記述します。
例１：出版社ごとの辞書の冊数を取得する
from django.models import Count
TrnBooks.objects.values(*["name","cnt"]).annotate(cnt=Count("name")).filter(genre = '辞書').values("name")
#select count(name) as cnt,name from TrnBooks where genre = '辞書' group by name;
例２：出版社ごとの辞書の売上実績を取得する。
from django.models import Sum
TrnBooks.objects.values(*["name","total"]).annotate(total=Sum("name")).filter(genre = '辞書').values("name")
#select count(name) as cnt,name from TrnBooks where genre = '辞書' group by name;
※ここで注意があります。グループ化がうまくいかない場合は、その条件（出力カラム）が必ず最大公約数、すなわちユニークな条件になっているか確認してください。一部SQLだとユニークな値でなくても最初の値が代表で表示されるように対応してくれる場合がありますが、ORMではそんな融通は利きません。また、後述するorder_byという条件にもグループ化のユニークさを妨げるカラム記述があると、ORMはそれに引きずられて、うまく統計がとれなくなる場合があります。
having
having句はグループ化されたものに対し、条件を付与する場合です。これはannotationの中に条件を付記していきます。
例：書籍トランザクションで10冊以上の辞書を持つ出版社を取得する
from django.models import Count
TrnBooks.objects.annotate(cnt=Count("name").filter(cnt__gte=10)).values(*["name","cnt"]).filter(genre = '辞書').values("name")
#select cnt(name) as cnt,name from TrnBooks where genre = '辞書' group by name having cnt >= 10;
結合
結合は A join B on 条件が基本です。
inner join
inner join（単純にjoin）とは等価結合のことで、同じ条件を持つカラムを結合したい場合、よくある例ではマスタにしかない値をトランザクションから取得したい時に用います。後述するouter joinとの違いは、null値は除外される点です。
また、djangoにおいて結合対象となるのは主キーか外部キーでユニーク指定している場合のみです。ORMでは一対多の関係ならばselect_ralatedを、多対一の関係ならばprefetch_relatedを用います。
例:書籍トランザクション上にある出版社一覧を取得（今回において、書籍トランザクションの出版社は名称ではなく、コード名のpub_codeとなっている）
TrnBooks.objects.prefetch_related('MstPublisher').values('name').distinct()
#select distinct(name) from MstPublisher join TrnBooks on MstPublisher.id = TrnPublisher.pub_code
outer join
outerjoinとは外部結合のことで、left outer join（left join）、right outer join（right join）などがありますが、早い話、加えられる側のnullありを認めるってことです。A left outer join BならばBのnull値を許容し、A right outer join BならばAのnull値を許容するという意味となります。
したがって、ORMでもfilterでnullを許容するように、isnull=Trueを追記すれば実現できるみたいです。
例:書籍トランザクション上にある出版社一覧を取得（今回において、書籍トランザクションの出版社は名称ではなく、コード名のpub_codeとなっており、出版社が記載されていないものもある）
TrnBooks.objects.filter(pub_code__isnull=True).values('name').distinct()
#select distinct(name) from MstPublisher right join TrnBooks on MstPublisher.id = TrnPublisher.pub_code
※注意：必model側の外部キーにnull=Trueと指定しておく必要があります（これがないと必ずinner joinかつwhere句にnull指定されてしまい、想定に反した問い合わせ結果になってしまいます）。
order by(並び替え順)
order by は並び替え順を決定します。デフォルトは昇順です。
例：書籍の名前を昇順で取得する
MstBooks.objects.order_by('name')
※降順の場合は、カラム名に-を付与するだけで実現可能です。
MstBooks.objects.order_by('-name')
※ちなみにorderは順番という名詞が知られていますが、順番に並べるという他動詞にも使え、その場合には方法をbyで表現します。
SQLではほかに、テーブルに対し論理集合を求めるunionや、条件分岐のwhenなどもありますが、正直プログラム構築においてあまり記述した記憶がないです（単純にDBMSからSQLで取得するならともかく）。一応それらも記述可能ですが、パフォーマンスが著しく低下する、あるいは保守性が低い記述なのであまり覚える必要ないと思います。
limit句は上位だけ取得したい場合は便利なので、また紹介しようと思います。
取得した値を展開する
次は値を展開する記述をまとめてみました。注意点はORM独自の記述と、Pythonでの記述をそのまま用いている例があり、その区別はある程度しておいた方が良いということです。
ループ式から取得する
値を取得する際にはvalues関数を用いる機会が多いです。また、個別のカラムを取得する場合はディクショナリ化されているので、value['hoge']となる点にも注意です。
ORMでvaluesを使用しない場合
ORMでvaluesを使用しない場合、ループ式の展開元に対し予めvaluesを付与します。
vals = MstBooks.objects.all()
for val in vals.values():
print(val) #全部
print(val['name']) #カラムがあった場合はそのカラムをディクショナリとして取得できる
ORMでvaluesを使用している場合
ORMでvaluesを既に使用している場合、ループ式はそのままとなります。
vals = MstBooks.objects.values('name','publisher')
for val in vals:
print(val)
print(val['name'])
テーブルデータを整理する
今度は取得テーブルデータに対し、逆にグループごとに、ディクショナリを使って階層化して格納していきます。今回は第一階層を出版社、第二階層をジャンルで分類していきます。
注意点として、動的にディクショナリを追加していくにはsetdefaultが必須で、これを設定しないとkey errorというエラー表示となります。そして、また、更にディクショナリを細分化していく場合は空ディクショナリを用意する必要があります。
そして、グループ化を終えて、複数のオブジェクトを格納していきたい場合にリストを利用するので、[]と空リストを指定します。
tables = {} #ディクショナリとして設定
for val in vals.values():
publisher = val['publisher']
genre = val['genre']
tables.setdefault(publisher,{}) #キー名は出版社にして空ディクショナリを追加する
tables[publisher].setdefault(genre,[]) #これ以上グループ化しないので空リストを用意
tables[publisher][genre].append(val) #リストに値を追加
print(tables)
※なお、上記の例はvalが複数あるという条件で記述しています。もし、これ以上細分化できないグループ化ならば、以下のような記述でも大丈夫です（集計済データの格納などに便利です）。
tables = {} #ディクショナリとして設定
for val in vals.values():
publisher = val['publisher']
genre = val['genre']
tables.setdefault(publisher,{})
tables[publisher].setdefault(genre,val) #これ以上細分化できない場合
print(tables)
ただしDjangoでテンプレートに値を展開することを考慮した場合、上記の方法の方がいいでしょう。
テンプレートで展開する
テンプレートで展開する場合は以下のように記述します。通常のループ文だとキーしか取得できないですが、以下のようにitemプロパティを付与すると、ディクショナリからキーと値の双方を取得できます（付与しない場合はリストのループ制御となります）。
※展開先の変数は何でもいいです。
{% for key,rows in tables.items %}
{% for key2,col in rows.items %}
{{ col }}<br>
{% endfor %}
{% endfor %}
個別の値を取得する
個別の値を取得するにはvalues_listでflat=True指定をすると対象カラムだけディクショナリを格納したリストとして取得できるので便利です。また、その際にfirstを用いるといいといわれてますが、このfirstはORMが持っているものではなく、リストの先頭を取得する役割を持ちます。
例:書籍トランザクションの出版社コードから出版社名を取得する
p_code = xxxx
pub_name = MstPublisher.objects.filter(pub_code = p_code).values_list('pub_name', flat=True).first()
ただし、firstを記述して問題がないのは、確実に参照できる状態にあることです。もし対象コードの値が存在しない場合pub_nameはNoneを返すので、firstを付与するとエラーとなります。その場合はpub_nameがNoneじゃないかの判定が必要です。
p_code = xxxx
pub_name = ''
result = MstPublisher.objects.filter(pub_code = p_code).values_list('pub_name', flat=True)
if(result):
pub_name = result.first()
特定のコードから、複数の値を参照したい場合
複数の値を参照したい場合はvalues_listでflat=Trueは使用できません。その場合はvalues_listにするとタプル化されて返されます。
例:書籍トランザクションの出版社コードから出版社名と創業年を取得する
p_code = xxxx
pub_names = MstPublisher.objects.filter(pub_code = p_code).values_list('pub_name','pub_year').first()
print(pub_names) #たぷとして表示される
もしも、複数の取得結果をディクショナリで取得したい場合は外部からF関数を用いvaluesメソッドで取得可能になります。その際、出力キー名はテーブル内のカラムと被らせないようにしてください。
from django.models import F
p_code = xxxx
pub_names = MstPublisher.objects.filter(pub_code = p_code).values(name=F('pub_name'),year=F('pub_year')).first()
print(p_code) #ディクショナリで表示される