高度なSQLをまとめてみる

業務でSQL Alchemyを使うことになり、
今まで「SQLはGPTに書かせればいいだろう」と思い、
selectやwhereくらいしか覚えていなかったが今大変。

SQL Alchemy の前提

SQL Alchemyはデータベースとのやり取りを
セッションで管理する

Lv.1　全取得

users = session.query(User).all()

Userのモデルをすべて取得する

Lv.2　カラム指定

users = session.query(
    User.name,
    User.email
).all()

Userモデルから"name"と"email"のみを取得する

Lv.3　エイリアス

users = session.query(
    User.name.label("user_id"),
    User.email
).all()

ここでは、User.name カラムに "user_id" というラベル（別名）を付けています。
User.name の値は user_id という属性としてアクセスされます。

• 用途
  • SQLクエリの結果で、より意味のあるまたはわかりやすい名前をカラムに付けたい場合。
  • ジョインやサブクエリなどで、同じ名前のカラムが複数存在する場合にそれらを区別する必要がある場合。

Lv.4　filterとfilter_byの違い

# filter
session.query(User).filter(
    User.name == 'Alice', 
    User.age > 30
)

# filter_by
session.query(User).filter_by(
    name='Alice'
)

• filter…SQLの WHERE 句と同様
• filter_by…主にキーワード引数を通じて等価比較を行います。
  • 等価比較（==）のみをサポート

Lv.5　サブクエリ

作成したサブクエリは、他のクエリの中で参照されたり、ジョインされたり、WHERE句で使われたりします。

# サンプルデータの追加
session.add_all([User(name='Alice', age=25), User(name='Bob', age=30)])
session.commit()

# サブクエリの作成
subquery = session.query(User.id).filter(User.age > 25).subquery()

# サブクエリを使用したメインクエリ
main_query = session.query(User).filter(User.id.in_(subquery))
result = main_query.all()

• 集計: グループ化されたデータに対してさらにクエリを実行する場合。
• ジョイン: サブクエリの結果を他のテーブルとジョインする場合。
• 複雑な条件: WHERE句でサブクエリを使って複雑な条件を指定する場合。

25歳以上のユーザーのidを取得している。
このクエリを毎回書くのは面倒なので
オブジェクトにしてしまおうという算段か。

また、サブクエリにもエイリアスをつけることができる。

# サブクエリの作成(サブクエリオブジェクト)
subq = session.query(User.name, User.email).filter(User.age > 30).subquery('user_view')

# エイリアスを使用してメインクエリを作成
main_query = session.query(subq.c.name, subq.c.email)

30歳以上のユーザーの名前とメールアドレスを取得するサブクエリを作成し、
このサブクエリに 'user_view' というエイリアスを付けています。
その後、メインクエリで subq.c.name や subq.c.email といった形式で
サブクエリのカラムにアクセスしています。

ここでの .c は、サブクエリのカラム（columns）にアクセスするための記法です。

Lv.6　select_from

query = session.query(User.name).select_from(User)
result = query.all()

FROM 句の明示的指定

この例では、User テーブルから name カラムを選択しています。select_from(User) により、FROM users 句が明示的に設定されます。

select_from は、クエリがどのテーブルを対象としているかを明確にするため、特に複雑なクエリの可読性を高めるのに役立ちます。

Lv.7　joinとouterjoin

join

join メソッドは、二つ以上のテーブルを内部結合（INNER JOIN）するために使用されます。内部結合では、両方のテーブルに共通するレコードのみが結果として返されます。

result = session.query(
    User, 
    Address
).join(
    Address, 
    User.id == Address.user_id
).all()

この例では、User テーブルと Address テーブルを User.id と Address.user_id で内部結合しています。

outerjoin

outerjoin メソッドは、二つ以上のテーブルを外部結合（LEFT OUTER JOIN）するために使用されます。外部結合では、左側のテーブルのすべてのレコードと、右側のテーブルのマッチするレコードが結果に含まれます。マッチしない場合、右側のテーブルのフィールドはNULLで返されます。

result = session.query(
    User, 
    Address
).outerjoin(
    Address, 
    User.id == Address.user_id
).all()

この例では、User と Address テーブルを外部結合しています。

Lv.8　func.COALECE

SQLの COALESCE 関数
COALESCE 関数は、引数リストの中で最初に見つかった非NULL値を返します。
もし提供されたすべての値がNULLである場合、COALESCE はNULLを返します。

# テーブルの例
example_table = Table('example', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('a', Integer),
                      Column('b', Integer))

metadata.create_all(engine)

# COALESCE関数の使用
stmt = select([
    example_table.c.id,
    func.coalesce(
        example_table.c.a, 
        example_table.c.b, 
        0
    )
])

この例では、example_table にあるカラム a と b から
最初に見つかった非NULL値を選択しています。
もし a と b が両方ともNULLである場合、0 が返されます。

# COALESCE関数の使用
query = (session.query(
    v.c.user_id,
    func.COALESCE(
        OtherModel.data_count > 0, false()
        )
        .label("data_count")
    )
)

• OtherModel.data_count > 0 は、この値が0より大きいか
• false(): false() はSQLAlchemyで偽（False）を表すための関数です。
• ラベルはエイリアス

COALESCE 関数は、
提供された引数リストの中で最初に見つかった
非NULL値を返します。

ここでは、
OtherModel.data_count > 0 の結果が
True または False になりますが、
もし NULL であれば false() が代わりに使われます。

つまり、data_count が 0 より大きい場合は
True を返し、
そうでない場合（0 以下、または NULL）は
False を返します。

要約すると、この文は OtherModel の data_count カラムが
0 より大きいかどうかをチェックし、
結果が True または False であることを保証し、
その結果に "data_count" という名前を付けています。

もし data_count が NULL であれば、
False が返されます。
これは、結果セットで data_count が
常にブーリアン値を持つことを保証するための方法です。