【MYSQL】最上位親フォルダの状況を確認したい

Question

閲覧頂きありがとうございます。

【概要】
ファイルテーブルからキーワードにヒットするファイルを取得したい。
しかし親フォルダのステータスは削除フラグが有効であるにもかかわらず、その親フォルダに属するファイルが取得される。
【質問】
親フォルダの削除フラグが有効の場合、そのフォルダに属するファイルのレコードは取得しないようにしたい。

”物理削除による参照元レコード連動削除”以外の方法で本件の解決策をご教示頂ければと思います。

環境はタグの通りになります。

以下追記

【テーブル構造】
■フォルダTBL
フォルダID:folder_id
削除フラグ：delete_flag
名前:name(text)
親フォルダID:parent_id（自フォルダが属するフォルダ）
┗FKフォルダTBL.フォルダID

■ファイルTBL
ファイルID:file_id
削除フラグ:delete_flag
名前:name(text)
フォルダID:folder_id（自ファイルが属するフォルダ）
┗FKフォルダTBL.フォルダID

Comments

[orange0190]

最低でもファイルテーブルの構造が分からないと答えようがないので、構造を教えてください。

[m.kosuda]

失礼致しました。テーブルの構造を追記致しました。

[orange0190]

これらのテーブルの運用について質問ですが、親フォルダに削除フラグが立っても、そこに属するフォルダ・ファイルには削除フラグは立たないのですか?

[m.kosuda]

テーブルの運用ですが、親フォルダの削除フラグをたっても、配下にあるフォルダ、ファイルに削除フラグはたてません。

[dupont_kedama]

質問です。
1. フォルダ階層（深さ）は無制限なのでしょうか。
2. 最上位のフォルダの親フォルダIDには何が入るのでしょうか。

[m.kosuda]

フォルダ階層は無制限となっております。最上位親フォルダの親フォルダIDは0（ゼロ）が入ります。

Answer 1

フォルダ構造が無制限とのことでしたので、私の認識での一般的な回答であろうことを書きます。

今のデータ構造でかつMySQLでこれをSQL文だけで実装するのは無理があります。
このデータ構造で期待する結果を取得するには（RDBでは）「再帰クエリ」と呼ばれる種類のSQLを実行する必要がありますが、MySQL/MariaDBに再帰クエリは実装されていません。

Comments

[m.kosuda]

なるほどですね。
PHP側で再帰的に削除フラグを子階層まで立てるようにしてみます。

Answer 2

以下で１行目でテーブルを繋げて、２行目で親フォルダにフラグの無いものを取得する例です。

select * from fileTBL inner join folderTBL ON 'fileTBL'.'folder_id' = 'folderTBL'.'id' ¥
where 'folderTBL'.'delete_flag' is NULL