php - 从一张 MySQL 表中删除父表和所有子表

Question

我有一个简单的类别表，具有一级嵌套。

+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(11)          | NO   | PRI | NULL                | auto_increment |
| name       | varchar(200)     | NO   |     | NULL                |                |
| parent_id  | int(11)          | YES  | MUL | 0                   |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| deleted_at | timestamp        | YES  |     | NULL                |                |
+------------+------------------+------+-----+---------------------+----------------+

我想准备 Eloquent 查询，它将按 ID 删除类别。如果提供了子类别 - 将其删除。但如果提供了根类别，请将其与所有子类别一起删除。

我有这样的东西:

\DB::table('categories AS c1')->whereIn('c1.id', function ($query) use ($id) {
        $query->select('c2.id')
            ->from('categories AS c2')
            ->whereNull('c2.deleted_at')
            ->where('c2.parent_id', $id);
    })
    ->orWhere('c1.id', $id)
    ->delete();

但我无法让它工作。我收到以下错误:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'as `c1` where `c1`.`id` in 
(select `c2`.`id` from `categories` as `c2` where `c2' at line 1 (SQL: delete from
`categories` as `c1` where `c1`.`id` in (select `c2`.`id` from `categories` as `c2` where
`c2`.`deleted_at` is null and `c2`.`parent_id` = 15) or `c1`.`id` = 15)

如何改进查询以使其正常工作？

Answer 1

假设您想要删除 id 等于 $id 或 parent_id 等于 $id 的项目，理论上可以执行以下操作:

DB::table('categories')->where('id', $id)->orWhere('parent_id', $id)->delete();

在现实生活中，您可能在 parent_id 字段上设置了外键约束，只要子类别存在，该约束就会阻止删除父类别。如果是这种情况，您有 2 个选择:

1. 运行 2 个查询:

   DB::table('类别')->where('parent_id', $id)->delete();DB::table('categories')->where('id', $id)->delete();

2. 定义约束时，将ON DELETE行为设置为CASCADE。这样，当父级被删除时，其所有子级也将被删除。您可以在定义外键约束时在迁移文件中执行此操作

   Schema::table('categories', function (Blueprint $table) {
     $table->foreign('parent_id')->references('id')->on('users')->onDelete('cascade');
   });