SQLを複数使う場合

Question

deleted_atがnullのデータだけ抽出しようと思っています。
また、親はparent_id is nul, 子はid = parent_idのように親子関係を持たせています。
なのでdeleted_at is not nullの親だったら子も抽出されないようにしようとしていますが上手く取り出せません。
したのphpでは１つ目のクエリで親のidを取得しています。そのidを$idに入れて、２つ目のクエリでparent_idが親のidと合致しているデータを抽出しようとしています。

$stmt = $db->prepare("select * from t_bbs where parent_id is null and deleted_at is null");
  $stmt->execute();
  $data =$stmt->fetchAll();
  
  foreach($data as $value) {
		$id = $value['id'];
  $stmt = $db->prepare("select * from t_bbs where deleted_at is null and (parent_id = $id or parent_id is null) order by id asc");
  $stmt->execute();
  $res = $stmt->fetchAll();
  print_r($res);
}

現状ですと最初だけ親子だけを取り出すのですが以降はずっと親だけ取り出してしまいます。

お力添えいただきたく思います！
お願い致します！

追記

テーブル定義です。テーブルは１つだけです。親と子だけなので２階層になります。

| t_bbs | CREATE TABLE `t_bbs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `body` text DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `post_user` varchar(8) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1590 DEFAULT CHARSET=utf8 |

+-----------------+
| version()       |
+-----------------+
| 10.4.12-MariaDB |
+-----------------+

Comments

[yodel]

t_bbsのテーブル定義を提示してください。。

[yambejp]

テーブル定義についてはcreate table形式で表示してください

[yodel]

今後でも良いので、テーブル定義は、ソースコード同様にマークアップするようにしてください。
掲示板のツリーを親子IDで表したテーブルだと思いますが、何階層下まで読み込む予定でしょうか？
MySQL8であれば、WITH句を利用したクエリが利用できます。質問時にDBバージョンも明記があると良いですね。

Answer 1

**「削除された親が存在しない」**という条件にすればよいだけのような？

select *
from t_bbs bbs
where deleted_at is null
  and not exists(
        select 1 from t_bbs 
        where id=bbs.parent_id and deleted_at is not null
      )

Comments

[退会済みユーザー]

それするならトリガー作成して子も論理削除では

[yodel]

コレですね。
そもそもテーブル設計どうなの、やりたい事何っていうのはありますが。。

[cacao86]

こちらを実行したら見事期待通りに動作しました！
うまい条件の視点が自分には持てていませんでした。
exists文も知らなかったので勉強させていただきます！
ありがとうございました！

Answer 2

ゴールが分からないですが、欲しい結果はこんな感じでしょうか？
インデックスが効かない設計、クエリだと思うので、性能面には気を付けてください。

select * from  t_bbs
where parent_id is null and deleted_at is null
union all
select child.* from t_bbs parent
inner join t_bbs child on parent.id = child.parent_id
where parent.deleted_at is null
and child.deleted_at is null;

Answer 3

なにをしたいかイマイチわかりませんが
別々に実行するのではなくjoinすればよいだけのように見受けられます

Comments

[yambejp]

とくにループ処理の中でsql文を発行するのはフロー的には
最悪だと思います

[cacao86]

ご回答ありがとうございます！
結合された場合
親は子の数分表示されてしまわれませんでしょうか？

ループ処理の中でsql文を発行するのは最悪なのですね。。。以後気をつけます。

[yambejp]

> 親は子の数分表示されてしまわれませんでしょうか？

親を表示するような処理が書いてないのでそんなことはないですが
子の数分親が表示されるのは正しい挙動です
逆に何をしたいのでしょうか？

[cacao86]

親id1, parent_id null
子id2, parent_id 1
子id3, parent_id 1
親id4, parent_id null
子id5, parent_id 4

上記のような形です。
$stmt=$db->prepare("select * from t_bbs as t1 left join t_bbs as t2 on t1.id = t2.parent_id where t1.deleted_at is null and t2.deleted_at is null ");
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($res);
このようにしたところ親が表示されなくなってしまいました。
親を飛ばして子だけが表示されています。
親も表示されると思ったのですが、どこに原因があるのでしょうか？

[yambejp]

なにかで詰まったときには「select * 」はやめましょう
必要なカラムを列記してください
joinする場合カラム名が競合する場合は別名をつけてください