SQLAlchemyにおけるサブクエリの使い方についての議事録です。
サブクエリを使用することで、複雑なクエリを1度のクエリで実行することができます。

ケース1：レコードの最新バージョンの取得

想定するシナリオは、各レコードには複数のバージョンがあり、最新バージョンのすべてのレコードを取得したいとします。
VersionedItem テーブルを仮定します。
item_version はアイテムの異なるバージョンを追跡し、item_id は各アイテムを一意に識別します。

VersionedItem テーブルのデータ:

item_id	item_version
1	1
2	1
3	2
4	2
5	3
6	3

ステップ1: 最新バージョンを見つけるためのサブクエリの作成

まず、VersionedItem テーブルから最も高いバージョン番号（つまり、最新バージョン）を返すサブクエリを作成する必要があります。SQLAlchemyでは以下のように記載します。

from sqlalchemy import func

# 最新バージョンを見つけるためのサブクエリを定義する
latest_version_subquery = db.session.query(
    func.max(VersionedItem.item_version).label('latest_version')
).subquery()

このサブクエリは、SQLAlchemyの func.max() を使用して item_version カラムの最大値を見つけ、最新バージョンを取得します。
label() メソッドは、集約関数の結果に名前をつけ、外部クエリで参照しやすくします。

ステップ2: メインクエリの作成

次に、このサブクエリを使用して、VersionedItem テーブルからこの最新バージョンのすべてのレコードを取得することができます。

# 最新バージョンに一致するすべてのアイテムを取得する
latest_version_items = db.session.query(VersionedItem).filter(
    VersionedItem.item_version == latest_version_subquery.c.latest_version
).order_by(
    VersionedItem.item_id
).all()

このクエリでは、latest_version_subquery.c.latest_version はサブクエリから計算された最新バージョンを参照します。
VersionedItem レコードを、その item_version がこの最新バージョンと一致するものに絞り込んでいます。
最後に、結果を item_id で並べ替えています。

latest_version_subquery.c.latest_versionにおける.cは、SQLAlchemyにおけるColumn（カラム）の略です。SQLAlchemyの文脈において、.cはサブクエリやテーブルのカラム属性にアクセスするために使用されます。

ケース2: 特定の条件にマッチするアイテムの選択

想定するシナリオは、ある商品テーブルから、特定の条件に一致する商品のIDリストに基づいて、商品詳細を取得するものです。
Product テーブルと Order テーブルがあり、特定の注文状態にある商品のみを抽出したいとします。

Product テーブルのデータ:

id	name	price	category	stock
1	スマートフォン	70000	電子機器	50
2	タブレット	40000	電子機器	30
3	ノートパソコン	120000	コンピュータ	20
4	イヤホン	15000	アクセサリ	60
5	コーヒーメーカー	25000	家電	15

Order テーブルのデータ:

id	product_id	quantity	status	order_date
1	1	1	shipped	2024-01-15
2	2	2	pending	2024-01-16
3	1	1	cancelled	2024-01-17
4	3	1	shipped	2024-01-18
5	4	3	pending	2024-01-19
6	5	1	shipped	2024-01-20
7	2	1	shipped	2024-01-21
8	4	2	shipped	2024-01-22

ステップ1: 注文状態で絞り込むサブクエリの定義

まず、注文状態が「出荷済み」の商品IDを抽出するサブクエリを定義します。

# 出荷済みの商品IDを抽出するサブクエリを定義
shipped_order_subquery = db.session.query(Order.product_id).filter(
    Order.status == 'shipped'
).subquery()

このサブクエリはOrderテーブルからstatusが「shipped」（出荷済み）のproduct_idを選択します。

ステップ2: IN句を使用したメインクエリの作成

次に、上記のサブクエリを使用して、Productテーブルから該当する商品を選択します。

# `IN`句を使用して、出荷済みの商品の詳細を取得する
shipped_products = db.session.query(Product).filter(
    Product.id.in_(shipped_order_subquery)
).all()

ここで、.in_()メソッドはSQLのIN句に相当し、サブクエリの結果（出荷済みの商品ID）に含まれるIDを持つProductテーブルのレコードを選択します。

.c 属性は主にサブクエリのカラムにアクセスする際に使用されますが、テーブルの直接のカラムにアクセスする場合は、.c 属性は必要ありません