突然ですが Python 使ってますか。
以下のような理由から、個人的には覚えておいて損は無いと思っています。

  • Web 開発・データ分析・機械学習・簡単なツール作成など様々な用途で利用できる
  • エディタや IDE との連携もサポートされていることが多いため開発効率も良い
  • Unix 系 OS には最初から入っている

今回はそんな Python を使った Web フレームワーク Django で、クエリを作成する際のあれこれについて書きます。

前提

Django のプロジェクト・アプリケーション作成についてはここでは触れません（公式ドキュメントや過去記事をご覧ください）。

まず、Django は基底クラスを利用するような仕組みになっており、用意された関数を継承して使うことが多いです。
ここでは get_queryset() 関数を継承して使います。
Django REST Framework などを使って Django を api サーバとして使う場合も基本的にはほぼ同じです。

where exists

商品をあらわす Product モデルとタグをあらわす Tag モデルがあったとして、これが1:Nの関係だったとします。
例えば検索機能で「指定されたタグを含む一覧を検索したい」という要件があった場合を考えます。

SQL イメージ
-- exists する対象のカラムは `x` とか 1 とかなんでも良いんですがここでは一旦 id で
select id, name from products where exists (select id from tags where products.id = tags.product_id);
Django 側コード
from django.db.models import Exists, OuterRef
from django.views.generic import ListView
# 以降 models に各モデルクラスがあり、関連が設定されているものとします
from app.models import Product, Tag

class ProductsView(ListView):
    model = Product

    def get_queryset(self):
        # model に定義したモデルクラスを利用して Queryset オブジェクトを生成
        queryset = super(ProductsView, self).get_queryset()

        # Exists を使ったフィールドを annotate を使ってマッピングする
        queryset = queryset.annotate(
            has_tag=Exists(
                Tag.objects.filter(
                    product_id=OuterRef('pk'),
                    name='test',
                ).values('id'), # values は指定したフィールドのみを辞書型で取り出すが、ここでは select するフィールドとして使う
            ),
        ).filter(has_tag=True)

        return queryset.values('id', 'name')

select でサブクエリ

購入履歴をあらわす PurchaseHistory クラスがあり、さらに購入したアイテムをあらわす PurchaseHistoryItem クラスがあるとします。
PurchaseHistoryItem クラスには product_id を設定し、どの商品を購入したかがわかるようにしています。
ここで「商品単位で購入件数を取得したい」というような場合を考えます。

SQL イメージ
select id, name, (select count(*) from purchase_history_items where products.id = purchase_history_items.product_id group by id) from products where exists (select id from tags where products.id = tags.product_id);
Django 側コード
from django.db.models import Count, IntegerField, OuterRef, Subquery
from django.views.generic import ListView
from app.models import Product, PurchaseHistoryItem

class ProductsView(ListView):
    model = Product

    def get_queryset(self):
        queryset = super(ProductsView, self).get_queryset()

        # Subquery を使ったサブクエリを作成し count の結果を annotate で紐づける
        subquery = PurchaseHistory.objects.filter(
            product_id=OuterRef('pk'),
        ).annotate(
            purchage_count=Count('id')
        ).values('purchage_count')

        # 何も考えずに subquery を指定すると、プライマリーキーで group by されてしまう
        # 他のカラムを指定して group by したい場合 QuerySet 経由で行えないため
        # QuerySet のプロパティ `query` で設定する必要がある
        subquery.query.set_group_by('product_id')

        queryset = queryset.annotate(
            purchase_count=Coalesce(Subquery(
                subquery,
                output_field=IntegerField(),
            ), 0),
        )

        # 集計結果が0件は除外する
        return queryset.filter(purchase_count__gt=0).values('id', 'name', 'purchase_count')

Case

type に応じて出しわけるラベルを変更するケースを考えます。
このケースだと本来はマスタを持つべきでしょうが、複雑な条件でも応用できるはずです。

SQL イメージ
select id, case type when 1 then '食品' when 2 then '衣類' else 'その他' end as type_name from products
Django 側コード
from django.db.models import Case, CharField, When, Value
from django.views.generic import ListView
from app.models import Product

class ProductsView(ListView):
    model = Product

    def get_queryset(self):
        queryset = super(ProductsView, self).get_queryset()

        # When 内に条件と表示する項目を定義する
        # カラムではなく値を利用する場合は Value を使う必要がある
        queryset = queryset.annotate(
            type_name=Case(
                When(
                    type=1,
                    then=Value('食品'),
                ),
                When(
                    type=2,
                    then=Value('衣類'),
                ),
                default=Value('その他'),
                output_field=CharField(),
            ),
        )

        return queryset.values('id', 'type_name')

集計

分類ごとに価格が一番高い商品を取得します。
シンプルなようで一工夫必要です。

SQL イメージ
select type, max(price) from products group by type;
Django 側コード
from django.db.models import Max
from django.views.generic import ListView
from app.models import Product

class ProductsView(ListView):
    model = Product

    def get_queryset(self):
        queryset = super(ProductsView, self).get_queryset()

        # 単純な集計なら aggregate が利用できる
        # この場合、戻り値は QuerySet オブジェクトではなくカラム名と値をもった辞書型になる
        # return queryset.aggregate(max_price=Max('price'))

        queryset = queryset.annotate(max_price=Max('price'))
        queryset.query.set_group_by('type')

        return queryset.values('type', 'max_price').all()

おわりに

書こうとしているクエリによっては「なんでここまで複雑なコードになるんや」ってのが結構あり、もういっそ SQL 直書きしてやろうかとなることも結構ありました（Model.objects.raw で SQL を直接書けます）。
この件はまだまだ書けそうなので、いずれ続編も書きたいと思います。

※2021/12/21追記 こちらにて続編を書きました。