Djangoで色々なパターンのクエリを書く・その2

この記事は Django Advent Calendar 2021 15日目の記事です。

今回は Django を使ったクエリ生成のあれこれについて書きます。
その2としているのは、以前も書いたことがあるからです。

Django、人気ですよね。
独立してから5年ぐらい立ちますが、これまで稼働したクライアントの中にも Django を使ったプロダクトがいくつかありました。
私自身、以前 Django で簡単な Web サイトを作っていました。

Django はフルスタックフレームワークですが、SPA やマイクロサービスといったワードもメジャーになってきた昨今だと API として使われることがほとんどな気がします。
Django は Django REST Framework という api 作成に特化したライブラリもあるので、比較的スムーズに開発できます。

今回紹介するもの

• OR
• SUM + GROUP BY
• RDBMS の独自関数
• （おまけ）WITH

コードはあくまでもクエリ生成部分に絞ったものなので、コード全体を見たいという方は以下リポジトリをご覧ください。

今回はクエリにフォーカスすることもあり、前述の Django REST Framework を使い api として作成しています。

OR

OR 条件に限らず、短いワードってとにかく検索しづらいんですよね。
公式ドキュメントにも記載がありますが、Q というオブジェクトを利用します（これまた検索しづらい名前ですが…）。

例えば商品タグ (tags) の検索において、code が指定されていた場合はその値を含むものを返す処理を考えます。
ただし、コードは設定しない (null) ということも可能で、そのようなデータは code が指定された場合でも検索結果に含めるものとします。
その場合は以下のようなコードになります。

Django のコード例

from django.db.models import Q
from api.models import Tag

queryset = Tag.objects

code = 'test' # 実際はリクエストパラメータなどから取得

if code:
    return queryset.select_related('product').filter(
        Q(code__contains=code) | Q(code__isnull=True),
    )

return queryset

生成されるクエリ

SELECT
  `tags`.`id`,
  `tags`.`product_id`,
  `tags`.`code`,
  `tags`.`name`,
  `products`.`id`,
  `products`.`name`,
  `products`.`type`,
  `products`.`price`
FROM
  `tags`
INNER JOIN `products` ON
  (`tags`.`product_id` = `products`.`id`)
WHERE
  (`tags`.`code` LIKE BINARY '%test%'
  OR `tags`.`code` IS NULL);

通常、filter 関数に複数のキーワードを含めると論理積 (AND) になります。
OR 検索をしたい場合は Q 関数内に条件を書き、論理和を意味する | で条件を結合します。
filter 関数を | でつなぐことでも同じ結果になります。

queryset.filter(code__contains=code) | queryset.filter(code__isnull=True)

SUM + GROUP BY

商品 (products.id) 単位の合計購入金額 (purchase_histories.price) を算出したいケースを考えます。

Django のコード例

from django.db.models import Sum
from django.db.models.functions import Coalesce

# 商品単位で購入金額を合計し、降順に取得
return queryset.select_related('product').values(
    'product_id', 'product__name',
).annotate(
    total=Coalesce(Sum('price'), 0), # 購入履歴が無い商品は0円とするため Coalesce を利用
).order_by('-total')

生成されるクエリ

SELECT
  `purchase_histories`.`product_id`,
  `products`.`name`,
  COALESCE(SUM(`purchase_histories`.`price`), 0) AS `total`
FROM
  `purchase_histories`
INNER JOIN `products` ON
  (`purchase_histories`.`product_id` = `products`.`id`)
GROUP BY
  `purchase_histories`.`product_id`,
  `products`.`name`
ORDER BY
  `total` DESC;

シンプルに見えるんですが一つ落とし穴があって、Django のバージョンによっては order_by を設定しないと正しく算出されないようです。
sum だけではなく count など他の集計関数を使った場合も同じことが言えそうです。
Stack Overflow にこの問題に関する質問と回答がありました。

RDBMS の独自関数

ある年ある商品の購入金額を、月単位に取得するケースを考えます。
グラフ表示する際などはこういったフォーマットでデータが返ってくると扱いやすいですよね。
MySQL でクエリを書く場合、DATE_FORMAT や YEAR を使って取得する方法が考えられますが、これを Django で実現する際には少し工夫が必要です。
関数実行には Func を、フィールドの動的な指定には F を利用します。

Django のコード例

from django.db.models import CharField, F, Func, IntegerField

# 商品ID: 1における2021年のデータを取得する
product_id = 1
year = 2021

return PurchaseHistory.objects.annotate(
    # 年でフィルタするため、YEAR 関数を使って購入日の年のみを算出する
    year=Func(
        F('purchase_date'),
        function='YEAR',
        template="%(function)s(%(expressions)s)",
        output_field=IntegerField(),
    ),
    # 年月単位でレコードを取得するため、DATE_FORMAT 関数を使って購入年月のみを算出する
    year_month=Func(
        F('purchase_date'),
        function='DATE_FORMAT',
        template="%(function)s(%(expressions)s, '%%%%Y/%%%%m')", # % はエスケープする必要がある
        output_field=CharField(),
    ),
).filter(
    product_id=product_id,
    year=year,
).annotate(
    total=Sum('price'), # 購入金額の合計を算出
).order_by('year_month').values('year_month', 'total') # 年月昇順に、年月と合計金額を取得する

生成されるクエリ

SELECT
  DATE_FORMAT(`purchase_histories`.`purchase_date`, '%Y/%m') AS `year_month`,
  SUM(`purchase_histories`.`price`) AS `total`
FROM
  `purchase_histories`
WHERE
  (`purchase_histories`.`product_id` = 1
  AND YEAR(`purchase_histories`.`purchase_date`) = 2021)
GROUP BY
  `purchase_histories`.`id`
ORDER BY
  `year_month` ASC;

WITH

mysql 8.0 では再帰 With 句が利用できます。
あるテーブルのレコードが、同一テーブルの親レコードを参照しているようなケースです。
with については Django に同等の関数が存在しないので、RawSQL を使って生 SQL を書くことで対応します。

Django のコード例

from django.db.models.expressions import RawSQL

tag = 'pc' # 実際はリクエストパラメータなどから取得

tag_subquery = Tag.objects.filter(
    product_id=OuterRef('pk'), # サブクエリとして利用するため、OuterRef を利用して親クエリのカラムを参照して結合する
).exclude(
    code='test',
)

# 自身を親に持つタグを再帰的に検索して、いずれかが紐づく商品に絞り込む
if tag:
    tag_subquery = tag_subquery.filter(
        id__in=RawSQL("""
            with recursive tag_group(id, parent_id) as (
                select
                    id, parent_id
                from
                    tags where code = %s
                union all
                select
                    tags.id, tags.parent_id
                from
                    tags
                inner join tag_group on tags.parent_id = tag_group.id
            )
            select
                tag_group.id
            from
                tag_group
        """, [tag]),
    )

queryset = Product.objects.annotate(
    has_tag=Exists(
        tag_subquery.values('id'),
    ),
).filter(has_tag=True)

生成されるクエリ

SELECT `products`.`id`, `products`.`name`, `products`.`type`, `products`.`price`, EXISTS(SELECT (1) AS `a` FROM `tags` U0 WHERE (U0.`product_id` = (`products`.`id`) AND NOT (U0.`code` = 'test' AND U0.`code` IS NOT NULL) AND U0.`id` IN (
    with recursive tag_group(id, parent_id) as (
        select
            id, parent_id
        from
            tags where code = 'pc'
        union all
        select
            tags.id, tags.parent_id
        from
            tags
        inner join tag_group on tags.parent_id = tag_group.id
    )
    select
        tag_group.id
    from
        tag_group
)) LIMIT 1) AS `has_tag` FROM `products` WHERE EXISTS(SELECT (1) AS `a` FROM `tags` U0 WHERE (U0.`product_id` = (`products`.`id`) AND NOT (U0.`code` = 'test' AND U0.`code` IS NOT NULL) AND U0.`id` IN (
    with recursive tag_group(id, parent_id) as (
        select
            id, parent_id
        from
            tags where code = 'pc'
        union all
        select
            tags.id, tags.parent_id
        from
            tags
        inner join tag_group on tags.parent_id = tag_group.id
    )
    select
        tag_group.id
    from
        tag_group
)) LIMIT 1) ORDER BY `products`.`type` ASC;

ただ、（Django が提供する関数を駆使して）もう少しうまく書ける気がしています。
詳しい方がいればぜひ教えて下さい。

まとめ

「思った以上にコード量が多くてややこしいな」とか「直接 SQL 書いたほうが早くね？」とか思われる方も多いかも知れませんが、正直私もそう思います。
ただ、Python や Django のサポートを受けながら SQL を組み立てていけるのは十分メリットだと思っています。
SQL は結局のところ文字列なので、コーディング中に気づきづらい（linter とかでチェックしづらい）という問題もあります。
それに 郷に入っては郷に従え という言葉もありますし、覚えていて損はない…はずです。
また機会があれば続編を書きたいと思います。