TL;DR
LEFT JOIN 結合条件 AND 論理削除の条件を使え。
JOIN句にはANDをつけられるぞ。
はじめに
論理削除とは、次のように削除されたかどうかの情報をカラムに持たせ、SQLを発行する際に常にWHERE句を使って、そのデータを取ってこないようにすることで、そのデータを削除されたかのようにユーザーに見せるテクニック、管理手法のことです。
論理削除の是非は置いておいて、このような設計がなされているテーブルを見ることは多いと思います。
| id | company_name | line_name | is_deleted |
|---|---|---|---|
| 1 | JR北海道 | 深名線 | true |
| 2 | JR北海道 | 名寄本線 | true |
| 3 | JR北海道 | 羽幌線 | true |
| 4 | JR東日本 | 東北本線 | false |
| 5 | JR東日本 | 磐越西線 | false |
| 6 | JR東日本 | 岩泉線 | true |
| 7 | JR東海 | 身延線 | false |
| 8 | JR東海 | 名松線 | false |
| 9 | JR西日本 | 三江線 | true |
| 10 | JR西日本 | 福塩線 | false |
| 11 | JR西日本 | 芸備線 | false |
| 12 | JR四国 | 高徳線 | false |
| 13 | JR四国 | 予土線 | false |
| 14 | JR九州 | 鹿児島本線 | false |
| 15 | JR九州 | 三角線 | false |
| 16 | JR九州 | 山野線 | true |
SELECT * FROM railways
WHERE is_deleted = false;
| id | company_name | line_name | is_deleted |
|---|---|---|---|
| 4 | JR東日本 | 東北本線 | false |
| 5 | JR東日本 | 磐越西線 | false |
| 7 | JR東海 | 身延線 | false |
| 8 | JR東海 | 名松線 | false |
| 10 | JR西日本 | 福塩線 | false |
| 11 | JR西日本 | 芸備線 | false |
| 12 | JR四国 | 高徳線 | false |
| 13 | JR四国 | 予土線 | false |
| 14 | JR九州 | 鹿児島本線 | false |
| 15 | JR九州 | 三角線 | false |
問題
次のようにテーブルが二つに分かれていて、結合先のテーブルに論理削除を効かせつつ、親テーブルの行の存在の情報だけは得たいとき。
companiesテーブル
| id | company_name |
|---|---|
| 1 | JR北海道 |
| 2 | JR東日本 |
| 3 | JR東海 |
| 4 | JR西日本 |
| 5 | JR四国 |
| 6 | JR九州 |
railwaysテーブル
| id | company_id | line_name | is_deleted |
|---|---|---|---|
| 1 | 1 | 深名線 | true |
| 2 | 1 | 名寄本線 | true |
| 3 | 1 | 羽幌線 | true |
| 4 | 2 | 東北本線 | false |
| 5 | 2 | 磐越西線 | false |
| 6 | 2 | 岩泉線 | true |
| 7 | 3 | 身延線 | false |
| 8 | 3 | 名松線 | false |
| 9 | 4 | 三江線 | true |
| 10 | 4 | 福塩線 | false |
| 11 | 4 | 芸備線 | false |
| 12 | 5 | 高徳線 | false |
| 13 | 5 | 予土線 | false |
| 14 | 5 | 鹿児島本線 | false |
| 15 | 6 | 三角線 | false |
| 16 | 6 | 山野線 | true |
こういう情報がほしい
| company_name | line_name |
|---|---|
| JR北海道 | NULL |
| JR東日本 | 東北本線 |
| JR東日本 | 磐越西線 |
| JR東海 | 身延線 |
| JR東海 | 名松線 |
| JR西日本 | 福塩線 |
| JR西日本 | 芸備線 |
| JR四国 | 高徳線 |
| JR四国 | 予土線 |
| JR九州 | 鹿児島本線 |
| JR九州 | 三角線 |
誤った解法
SELECT *
FROM companies
LEFT JOIN railways ON company.id = railways
WHERE railways.is_deleted = false;
これだと論理削除された行がまるごと吹き飛んでしまうので、JR北海道の存在がなくなってしまいます。
解決
SELECT *
FROM companies
LEFT JOIN railways ON company.id = railways
AND railways.is_deleted = false;
結合先に論理削除がある場合は、JOIN ONの条件を複数にしてください。これで望むようなデータが得られます。
中間テーブルがあるようなリレーションでも
次のように中間テーブルがあるようなリレーションでも同様のことができます
students
| id | name |
|---|---|
| 1 | 一郎 |
| 2 | 二郎 |
| 3 | 三郎 |
student_subject
| id | student_id | subject_id | is_cancelled |
|---|---|---|---|
| 1 | 1 | 1 | 0 |
| 2 | 1 | 2 | 0 |
| 3 | 1 | 4 | 0 |
| 4 | 2 | 1 | 0 |
| 5 | 2 | 2 | 0 |
| 6 | 2 | 3 | 0 |
| 7 | 3 | 2 | 1 |
| 8 | 3 | 3 | 1 |
subjects
| id | name | is_cancelled |
|---|---|---|
| 1 | 微分積分学 | 0 |
| 2 | 有機化学 | 0 |
| 3 | 無機化学 | 0 |
| 4 | 物理化学 | 1 |
SELECT *
FROM students st
LEFT JOIN student_subject ss ON st.id = ss.student_id AND ss.is_cancelled = 0
LEFT JOIN subjects sb ON ss.subject_id = sb.id AND sb.is_cancelled = 0
以下のようなデータが得られるはずです。
| name | subject |
|---|---|
| 一郎 | 微分積分学 |
| 一郎 | 有機化学 |
| 二郎 | 微分積分学 |
| 二郎 | 有機化学 |
| 二郎 | 無機化学 |
| 三郎 | NULL |
ORMのあるようなフレームワークでは
下記のような記事が参考になるかと思います。
EloquentのJOINで結合テーブルに論理削除を効かせる
Qiita https://qiita.com/danishi/items/726fcbed683c03ef389e
【Rails】結合先のテーブルで条件つけたいけど, 結合元のレコードは全部欲しいってときはScoped Association | SharpKnock
https://sharpknock.com/posts/programming/scoped-association.html