Zenn
😎

djangoのORMの資料(walkalongs)

に公開
Django
SQL
ORM
tech

web版

https://julkaar9.github.io/walkalongs/
のサイトをざーっとみていけば、SQLとdjangoのORMの関係の理解が深まると思われる


図の赤枠あたりのものをクリックし
右クリで日本語に翻訳し、ざーっと眺めたり
必要に応じて検索して、SQLとdjangoのORMに対応関係について理解を深めたり
業務で必要な時に活用する。

github

https://github.com/julkaar9/walkalongs
で、上記のweb版の情報を
git clone https://github.com/julkaar9/walkalongs.git
で、ローカルに落としてこれると思われる。

web版だけで十分かと思うが、一応、紹介した。

以下は補足事項

ORMのqueryが意図通りのSQLの状況になってるかの確認方法(リバースエンジニアリング)

方法1

sql, params = qs.query.sql_with_params()
print(sql % params)

のようにする。
sqlには%sが含まれたフォーマット文字列が入ってて、
paramsには、実際のパラメータ値がリストやタプルで入ってる
それを%sに当てはめてのsqlの文字列にするのが
sql % params
の演算で、それをprint()してる

% は、Python の旧式フォーマット演算子
で、
sql, params = qs.query.sql_with_params()
が、そのやり方になっとるから、しゃーない。

ちなみに、Python の新式フォーマット演算子
は、
"SELECT ... id={} AND name={}".format(42, "Bob")
みたいな感じのやつ

方法2

print(str(qs.query))
で、%sのプレースホルダ付きSQLを得る

上記の方法1，方法2の例は下記のとおり

from django.db import connection
qs = (
    User.objects            # INNER / LEFT JOIN 自動生成
        .filter(groups__name='staff')
        .annotate(cnt=Count('orders'))
        .filter(cnt__gt=5)  # HAVING 相当
        .distinct()
)

sql, params = qs.query.sql_with_params()  # Django 4.0+
print(sql % params)                       # ← 完全な SQL 文字列

# あるいは単に
print(str(qs.query))                      # %s プレースホルダ付き SQL

親子のイディオム by ( Django 5.2 )

class Customers(models.Model):
    customer_number = models.BigAutoField(primary_key=True)
    # …

class Orders(models.Model):
    customer_number = models.ForeignKey(
        Customers,
        db_column="customer_number",
        on_delete=models.CASCADE,
    )
    # …

親（Customers）→子（Orders）で INNER JOIN を出す唯一の書き方

  • 親→子で 子が必要
  • .filter(orders__isnull=False)
  • INNER JOIN
# Customers に少なくとも 1 件の Order が付いている行だけ
inner_qs = (
    Customers.objects
             .filter(orders__pk__isnull=False)   # ←ここが鍵
             .distinct()                         # 重複行を除くなら付ける
)

print(str(inner_qs.query))

*****

SELECT "customers".*
FROM   "customers"
INNER JOIN "orders"
       ON ("customers"."customer_number" = "orders"."customer_number_id")
WHERE  "orders"."id" IS NOT NULL;

親→子で LEFT OUTER JOIN を出す標準的な書き方

from django.db.models import Count

left_qs = (
    Customers.objects
             .annotate(order_cnt=Count("orders"))   # 子を参照するだけ
)

print(str(left_qs.query))

****

SELECT "customers".*,
       COUNT("orders"."id") AS "order_cnt"
FROM   "customers"
LEFT OUTER JOIN "orders"
       ON ("customers"."customer_number" = "orders"."customer_number_id")
GROUP BY "customers"."customer_number";

親 → 子 LEFT OUTER JOIN を “集計なし” で出す最小コード

# Customers が親、Orders が子
left_qs = (
    Customers.objects           # 親テーブル側からスタート
             .values(           # ただ列を抜き出すだけ
                 "customer_number",          # 親カラム
                 "customer_name",
                 "orders__id",               # 子カラムを参照
                 "orders__order_date",
             )
)

print(str(left_qs.query))

********

SELECT
    customers.customer_number,
    customers.customer_name,
    orders.id              AS orders__id,
    orders.order_date      AS orders__order_date
FROM customers
LEFT OUTER JOIN orders
       ON customers.customer_number = orders.customer_number_id;

補足

Django ORM は「nullable かどうか」ではなく、「親 → 子方向の参照かつ、明示的にフィルタしないかどうか」で LEFT OUTER JOIN を使用すべきと判断します。
つまり：

.filter(orders__id=…) → INNER JOIN

.values("orders__id") や .annotate(order_id=F("orders__id")) → LEFT OUTER JOIN

JOIN の種類は「参照の有無」と「条件の有無」で分岐している。

https://docs.djangoproject.com/en/5.2/ref/models/querysets/#values
で、
これは明確に、values() や annotate() 内で 逆参照（例: orders__id） を行ったとき、Django が LEFT OUTER JOIN を発行することが明記。

Djangoにおける「逆参照」とは、外部キーを持っていない側（親）から、外部キーを持つ側（子）を参照することです。

class Customer(models.Model):
    ...

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=...)

このとき、Order → Customer は通常の参照、
Customer → Order は 逆参照 です。

Customer.objects.values("orders__id") のように orders__... と書くのが逆参照です。

qs = Customers.objects.values("customer_number", "orders__id")
print(str(qs.query))

*****

SELECT
  "customers"."customer_number",
  "orders"."id" AS "orders__id"
FROM "customers"
LEFT OUTER JOIN "orders"
  ON ("customers"."customer_number" = "orders"."customer_number_id")

from django.db.models import F

# Customers が親、Orders が子
qs = (
    Customers.objects
             .annotate(latest_order_date=F("orders__order_date"))
             .values("customer_number", "latest_order_date")
)

print(str(qs.query))

****

SELECT
    customers.customer_number,
    orders.order_date AS latest_order_date
FROM customers
LEFT OUTER JOIN orders
       ON customers.customer_number = orders.customer_number_id;

親子孫のイディオム by ( Django 5.2 )

class Customers(models.Model):
    customer_number = models.BigAutoField(primary_key=True)
    customer_name   = models.CharField(max_length=100)

class Orders(models.Model):
    customer_number = models.ForeignKey(
        Customers, db_column="customer_number",
        on_delete=models.CASCADE, null=False      # 親は必須
    )
    order_date = models.DateField()

class OrderLines(models.Model):
    order = models.ForeignKey(
        Orders, related_name="lines",
        on_delete=models.CASCADE, null=True       # 子は任意
    )
    item = models.CharField(max_length=100)

すべて LEFT OUTER JOIN

qs_left_all = (
    Customers.objects
             .values(
                 "customer_number",
                 "customer_name",
                 "orders__id",
                 "orders__order_date",
                 "orders__lines__id",
                 "orders__lines__item",
             )
)

*********

SELECT
    "customers"."customer_number",
    "customers"."customer_name",
    "orders"."id"               AS "orders__id",
    "orders"."order_date"       AS "orders__order_date",
    "orderlines"."id"           AS "orders__lines__id",
    "orderlines"."item"         AS "orders__lines__item"
FROM   "customers"
LEFT OUTER JOIN "orders"
       ON ("customers"."customer_number" = "orders"."customer_number_id")
LEFT OUTER JOIN "orderlines"
       ON ("orders"."id" = "orderlines"."order_id");

すべて INNER JOIN

qs_inner_all = (
    OrderLines.objects
              .select_related("order", "order__customer_number")
)

********

SELECT
    "orderlines"."id",
    "orderlines"."item",
    "orderlines"."order_id",
    "orders"."id"               AS "order_id",
    "orders"."order_date",
    "orders"."customer_number_id",
    "customers"."customer_number",
    "customers"."customer_name"
FROM   "orderlines"
INNER JOIN "orders"
       ON ("orderlines"."order_id" = "orders"."id")
INNER JOIN "customers"
       ON ("orders"."customer_number_id" = "customers"."customer_number");

親–子 INNER、子–孫 LEFT OUTER

qs_mix1 = (
    Customers.objects
             .filter(orders__pk__isnull=False)        # 親に子がある行だけ
             .values(
                 "customer_number",
                 "customer_name",
                 "orders__lines__id",
                 "orders__lines__item",
             )
)

**********

SELECT
    "customers"."customer_number",
    "customers"."customer_name",
    "orderlines"."id"     AS "orders__lines__id",
    "orderlines"."item"   AS "orders__lines__item"
FROM   "customers"
INNER JOIN "orders"
       ON ("customers"."customer_number" = "orders"."customer_number_id")
LEFT OUTER JOIN "orderlines"
       ON ("orders"."id" = "orderlines"."order_id")
WHERE  "orders"."id" IS NOT NULL;

親–子 LEFT OUTER、子–孫 INNER

qs_mix2 = (
    Customers.objects
             .values("customer_number", "customer_name")
             .filter(orders__lines__id__isnull=False)  # 孫がある行だけ残す
)

*****

SELECT
    "customers"."customer_number",
    "customers"."customer_name"
FROM   "customers"
LEFT OUTER JOIN "orders"
       ON ("customers"."customer_number" = "orders"."customer_number_id")
INNER JOIN "orderlines"
       ON ("orders"."id" = "orderlines"."order_id")
WHERE  "orderlines"."id" IS NOT NULL;

Discussion