論理削除の絞り込みはWHERE句でやるな

これはなに こんにちは、レバテック開発部のもりたです。 論理削除、皆さんは採用していますか？ わたしが普段開発するシステムでは論理削除を採用しているものもあるのですが、今回はその論理削除の気を付けるべき点として「子テーブルの論理削除されたレコードの絞り込みをWHERE句でしてはならない」という問題について解説します。慣習的に起こりにくいミスなんですが、案外ダメなことを知らない人もいると思うので、ご紹介です。 どうすればいいか？ こうじゃなくて... SELECT * FROM parents LEFT JOIN children ON parents.id = children.parents_id WHERE parents.deleted_at IS NULL AND children.deleted_at IS NULL -- 子テーブルの論理削除の絞り込み ; SELECT * F

[mak_in]

個人的には、論理削除のように、毎回必ず同じwhere条件をつけるものは、テーブルを分けるように設計してる。アーキテクトとしては、いつか誰かがやらかす可能性のある設計はできるだけ潰しておきたいところ。

[mohno]

そりゃ、LEFT JOINなのにchildrenテーブルをparentsの抽出条件に入れちゃダメ、という話なのでは。「不具合」というより仕様というか。「親テーブルの絞り込みはWHERE句で大丈夫です」

[soccermono]

参照以外の作業をする場合、left join 使うのがおかしい

[mcq]

WHERE句っていう文法の問題じゃなくて、子テーブルのフィルタリングとJOIN演算の適用順序を意識する必要がある、という話

[kumoha683]

IS NULLでしか論理削除が判定できない データベースの設計がおかしいのでは？

[shikiarai]

nullは不定値だ。論理削除はしてるかしてないかなんだからtrue/falseか1/0で判断しろ

[n_231]

具体的なケースの具体的なクエリが無いのでアレだが、where 区でもnot exists ちゃんと書けば済む話では？ 論理削除チェックにnull使ってるのも原因か。普通にflagならこんなややこしいことにならない。

[Eiichiro]

どういう場合に子要素が全部削除されてないのに、親要素に削除フラグ立てるのかの利用ケースが思い浮かばない。 子要素がすべて削除もしくは子要素が０の場合削除フラグ立てるのはあり得るけど。

[otchy210]

ベスト→論理削除フラグ使わない、ベター→ビューを作って常にそれを使う、だと思ってる。

[punychan]

parents側の論理削除はここでは無関係だね。LEFT JOINする右側テーブルで論理削除されたレコードをないものとしたければ、JOIN時に条件つけろということ。

[toro-chan]

WHERE句以前に、情報技術から言って、親テーブル側で判定したら包含関係が適切じゃない。写像して全件マッチしたら処理が遅くなるに決まってる。それが分からない人は数学から勉強したほうがいい

[hiroshe]

WHERE使っとるやん、日本語おかしいやろ

[greenbow]

SQLの評価順を理解していないと間違えるよ、というだけで論理削除は関係ないのでは。WHERE とJOINどちらの例が正しいかは仕様によるし一般化できる話でもないような

[soxandcity]

期待する振る舞いのテストを書くようにすればいいんじゃないかな

[Iridium]

ブクマがメチャクチャ割れていて定見がないのがわかる。絶対使うものなんだからDBがシステムとして実装しなよ。

[da-yoshi]

JOINに書くのは結合条件で、WHEREに書くのはデータ全体の取得条件。今回の場合、論理削除された子テーブルをJOIN対象から外すだけの時は結合条件、紐づく親データも対象から外すなら取得条件になる。後者はLEFT JOINと矛盾

[irh_nishi]

普通はVIEW作るんじゃないの？そんでselectに書かなきゃ問題ないよね。。。

[igrep]

タイトルが悪い。

[suika3417]

ソフトデリートにタイムスタンプを入れる系は、 ただのフラグだとユニークが死ぬからだろうな…

[runtothehill32]

ブコメ大半はちゃんと理解されていないなあ。あるあるある。

[n314]

論理削除は関係ないような？部署一覧と所属する65才以上の社員一覧、みたいな問いあわせでも同じで、一般的な結合と絞り込みの問題では。

[kagerou_ts]

結合したあとでwhere適用するから実質INNER JOINになるって話かな。たしかにややこい

[shoh8]

ぶっ叩かれてるの、いいね。/こういう時は当事者意識を薄めて、反応の中からスキルアップにつながりそうな文面だけ受け取るのが吉

[sabinezu]

検索結果件数どうすんの。"予約取り消しが論理削除で実装されていた場合、この商品は予約が１件も入っていないのにもかかわらず抽出対象から外れてしまいます"商品検索なのか予約検索なのか仕様がおかしいだけだろ。

[daishi_n]

論理削除フラグはNOT NULL定義にするのが基本。カーディナリティが低いとインデックス使わないからこの辺は要検討

[sailoroji]

何言ってんだこいつ

[knitcapmann]

論理削除の判定をNULLでやってる仕様がマズい。

[queeuq]

要件変わっとるやんけ。ただのバグだろ

[soulfulmiddleagedman]

DB

[ultrabox]

その昔、Oracleでは外部結合を(+)って書いてた時期があってぇ

[strawberryhunter]

そもそも論理削除が有り得ない。論理削除のような機能が必要で1つのテーブルで実現するなら、きちんとした業務的な名前が付いた列で表現して普通にWHERE句だろ。シンプルにしておけよ。

[mk_55]

IS NULLってインデックス効かないんじゃなかった？と思ったらちゃんと効くらしい

[daichirata]

主張はわかるけど、評価順とかそういう込み入った話では無い気が。最初のクエリは parent and child になってるんだからそりゃそうだよね感が。

[kobito19]

サブクエリー使う方が自然では。 select * from children where deleted_at is null と left join しろ / " 参照以外の作業をする場合、left join 使うのがおかしい" は何が言いたいのか意味が分からん

[odakaho]

そもそも親を取りたいのに LEFT JOIN した子を抽出条件にするのが実装ミスくさいってことね。

[onesplat]

いろいろ間違ってて草

[aktkro]

理解不能

[Fluss_kawa]

JOIN結果が想像できてないだけでは？最適化がどこまで効くかにもよるけどWITHとかでテーブル細かく作っていけば抜け漏れなく想像しやすくなるよ。たぶん。

[FreeCatWork]

WHERE句はダメにゃ！ボクが優しく教えてあげるにゃ！