DjangoのSubqueryを駆使して難解な条件をクリアする

この記事は JSL (日本システム技研) Advent Calendar 2019 - Qiita の12月9日の記事です(でした)。
昨年のカレンダーはこちらから

皆さんお久しぶりです。
月歩人です。

本日はアドベントカレンダー担当日ということで日をまたぐ前提の記事を書きます。
内容はあまり使うことはなさそうだけど知っておくと便利、でも日本語の記事はあまりないクエリ式の「Subquery」についてです。

Subqueryとは？

Djangoが提供するクエリ式の一部です。
ドキュメントではここで説明されています。

クエリ式の中ではF式を使う機会が多いと思いますが、Subqueryとはその名の通りDjangoでサブクエリを組み立てる時に使用するモジュールです。
ググって出てくるのは、皆さんもお世話になっていることが多いと思われるtokibito氏のBlog「偏った言語信者の垂れ流し」がとても参考になります。

なぜSubqueryを使うに至ったか

私がプライベートで開発していたDjangoのプロダクトの話です。
そのプロダクトの要求条件はとても複雑であり、必然的にモデルも精密さが要求され、当初要求していた条件に最適なモデルで開発を進めていました。

しかしプロダクト開発ではあるあるな開発中の追加仕様が、単純なORMでは実現できないということがわかったことが発端です。

調査過程は割愛しますが、結果としてSubqueryを使えばなんとかなる！という結論に至りました。

(今思い返すと、Window関数でうまくやれたんじゃという気持ちが。。。)

問題のモデル構造(渾身のER図)

月歩人渾身の物理ボードが出てきましたね
この間に1to1のモデルとかもありますが、ややこしくなる(書ききれない)ので割愛。

問題の条件

• ListViewでUモデルのquerysetを表示する
• クエリパラメータk_idのKモデルオブジェクトを親に持つモデルBの数だけオブジェクト取得する

単純にこれだけであれば

U.objects.filter(k_id=k_id)

と考えるかもしれませんが、この時点で条件的にアウトです。
* Uモデル1件
* Kモデル1件
* Bモデル2件(どちらもKオブジェクトが親)

の場合、上記ORMではUオブジェクトは1件取得できますが、 クエリパラメータk_idのKモデルオブジェクトを親に持つモデルBでの数だけオブジェクトを取得する という条件を考えると

b_list = B.objects.filter(K_id=k_id)
U.objects.filter(b__in=b_list)

が正解です。
これだとUのquerysetが2つになり、条件を1つクリアできます。
ここではあえてdistinctを噛ませません。

また、UモデルのquerysetはDjango templateで表示し、さらにpagination、追加表示項目を表示する必要がありました。
そんな頭を悩ませる原因となってしまった条件が以下です。

• Cモデルが持つbooleanフィールドboolean_Dをtemplate上で表示したい
  • UモデルとCモデルは1:mの関係だが、UモデルとBモデルでunique_togetherとなっている
• Pモデルが持つcharフィールドNをtemplate上で表示したい
• Pモデルが持つintフィールドOで昇順ソートを行いたい
• Pモデルの作成日であるDateフィールドで降順ソートを行いたい

テンプレートは以下

...
{% for u in page_obj.object_list %}
    <tr>
        <td>{{ u.name }}</td>
        {% if u.D %}
            <td>DONE</td>
        {% else %}
            <td><strong>UNDONE</strong></td>
        {% endif %}
    </tr>
{% endfor %}
...

さて、わけがわからなくなってきました
Uモデルから遠いところにあるモデルのフィールド値はどうすれば参照できるのか…

問題の本質

• paginationをListViewで実現することから、dictで頑張ってtemplateでゴリ押しするということがほぼ不可能
• 単純なORMでは条件をクリアできない

クエリを作ってはカスタマイズし、作ってはカスタマイズしを繰り返し、頭を悩ませ時に発狂し、この条件を満たすクエリを考えるだけで数日使ったと思います
ちなみに当初はdictを使って頑張りましたが、template側で使うpagecounterやnext、previousなどのpagination関係で詰みました。

クエリ式 RawSQLを使う

ここでポイントになるのがBモデルです。
拡張条件にはCモデルとPモデルのフィールドが出てきますが、それらを絞り込むためにはBモデルが不可欠です。

しかし、django ORMではrelation先のidをデフォルトではプロパティとして表示できません。

SELECT U.id, U.created_at, U.modified_at, U.user_id, U.k_id FROM U
 INNER JOIN K ON (U.k_id = K.id)
 INNER JOIN B ON (K.id = B.k_id)
 WHERE B.id IN (SELECT U0.id FROM B U0 INNER JOIN K U1 ON (U0.k_id = U1.id)

どうすればよいか？
そんな時はannotate とクエリ式であるRawSQLを使います。

b_list = B.objects.filter(K_id=k_id)
U.objects.filter(b__in=b_list).annotate(B_id=RawSQL('B.id', ()))

# ↑BはJoinしているので、RawSQLでidだけselectに追加する

このようにするとsqlが以下のように変化します

SELECT U.id, U.created_at, U.modified_at, U.user_id, U.k_id,
 (B.id) AS "B_id" FROM U
 INNER JOIN K ON (U.k_id = K.id)
 INNER JOIN B ON (K.id = B.k_id)
 WHERE B.id IN (SELECT U0.id FROM B U0 INNER JOIN K U1 ON (U0.k_id = U1.id)

# (B.id) AS "B_id" というフィールドが追加された

この時点でB_idがオブジェクトごとにことなることから、distinctをかませてもgroup_byされません。
(RawSQLに関してはだいぶ乱暴な使い方をしている気がします)

注意点

似たようなものでobjectsにraw()がありますが、
こちらでselectを追加してもSubqueryでは利用できないため今回のようなケースでは使わないようにしましょう。

さて、これでBモデルのidがUモデルクエリセットからプロパティとして参照できるようになりました。

クエリ式 SubqueryとOuterRefを使う

いよいよメインのSubqueryです。
まずは

• Cモデルが持つbooleanフィールドboolean_Dをtemplate上で表示したい

という条件をクリアしましょう。

• UモデルとCモデルは1:mの関係だが、UモデルとBモデルでunique_togetherとなっている

ということなので、前項でBモデルidをselectに追加したことでSubqueryとOuterRefを利用できるようになりました。

b_list = B.objects.filter(K_id=k_id)
U.objects.filter(b__in=b_list).annotate(
  B_id=RawSQL('B.id', ()),
  boolean_D=Subquery(
    C.objects.filter(u_id=OuterRef('pk'), b_id=OuterRef('B_id')).values('boolean_D'),
  )
)

上記クエリ文では、boolean_DをSubqueryで出しています。
気をつけたいのが、

• オブジェクトに対して1:1になること
• valuesで単一のフィールドを指定すること

ということです。
例えばこの場合にu_id=OuterRef('pk')またはb_id=OuterRef('B_id'))が無かった場合はユニークではなくなるため例外が発生します。

OuterRefは外部クエリの値を参照する時に使うクエリ式です。
OuterRef('pk')はUモデルのpk=idであり、OuterRef('B_id')はRawSQLで追加したB_idフィールドです。
CモデルはUモデルとBモデルの組み合わせでユニークになるため、上記サブクエリが実行可能となります。

SQL文は以下

SELECT U.id, U.created_at, U.modified_at, U.user_id, U.k_id, (B.id) AS B_id,
 (SELECT U0.boolean_D FROM C U0 WHERE (U0.b_id = ((B.id)) AND U0.user_group_id = (U.id))) AS boolean_D FROM U
 INNER JOIN K ON (U.k_id = K.id)
 INNER JOIN B ON (K.id = B.k_id)
 WHERE B.id IN (SELECT U0.id FROM B U0 INNER JOIN K U1 ON (U0.k_id = U1.id)

これで条件の1つであるboolean_DフィールドがUモデルクエリセットの属性として追加されました。

残りは以下の3つです

• Pモデルが持つcharフィールドNをtemplate上で表示したい
• Pモデルが持つintフィールドOで昇順ソートを行いたい
• Pモデルの作成日であるDateフィールドで降順ソートを行いたい

PモデルはB_idがあることで単一に絞り込めますので、同じようにSubqueryを使用します

b_list = B.objects.filter(K_id=k_id)
U.objects.filter(b__in=b_list).annotate(
  B_id=RawSQL('B.id', ()),
  boolean_D=Subquery(
    C.objects.filter(u_id=OuterRef('pk'), b_id=OuterRef('B_id')).values('boolean_D'),
  ),
  N=Subquery(
    P.objects.filter(b=OuterRef('B_id')).values('N'),
  ),
  O=Subquery(
    P.objects.filter(b=OuterRef('B_id')).values('O'),
  ),
  P_created_at=Subquery(
    P.objects.filter(b=OuterRef('B_id')).values('created_at'),
  ),
)

SQLは以下

SELECT U.id, U.created_at, U.modified_at, U.user_id, U.k_id, (B.id) AS B_id,
 (SELECT U0.boolean_D FROM C U0 WHERE (U0.b_id = ((B.id)) AND U0.user_group_id = (U.id))) AS boolean_D,
 (SELECT U0.N FROM P U0 INNER JOIN B U1 ON (U0.id = U1.p_id) WHERE U1.id = ((B.id))) AS N,
 (SELECT U0.O FROM P U0 INNER JOIN B U1 ON (U0.id = U1.p_id) WHERE U1.id = ((B.id))) AS O,
 (SELECT U0.created_at FROM P U0 INNER JOIN B U1 ON (U0.id = U1.p_id) WHERE U1.id = ((B.id))) AS P_created_at
 FROM U
 INNER JOIN K ON (U.k_id = K.id)
 INNER JOIN B ON (K.id = B.k_id)
 WHERE B.id IN (SELECT U0.id FROM B U0 INNER JOIN K U1 ON (U0.k_id = U1.id)

最後に上記クエリセットにSubqueryで定義したorder_byを実装すれば条件はクリアです

query.order_by('O', '-P_created_at')

これでUモデルのクエリセットがCモデル、Pモデル、Bモデルを内包したクエリセットに生まれ変わりました。

最後に

以上のことからDjango ORMに任せるとORMでサブクエリは実現できてもSQLはだいぶ残念なことになるということがわかりました。
(Pモデルのサブクエリをいちいち定義しなくてもannotateで宣言できる方法あったら教えて欲しい...)
Django ORMは便利ですが、かゆいところに手が届かない場合はハマりますね。

今回のSubqueryは積極的に使うことがないかと思いますが、
プロダクト開発が進むとこういった問題が発生することもあるかと思いますので覚えておいて損はありません。
今回はSubqueryをメインに紹介しましたが、Existsというサブクエリの判定式もあります。
使い分けて活用してみて下さい。