php larvel 加入所有表时删除多个表中的数据

Question

我的代码有问题。我必须使用 id 从 5 个表中删除数据，这些表由主键和外键连接。这是我尝试过的，但它会显示添加订阅表 ID。但是已经添加了订阅表 id。$subscription = DB::table('tbl_asset_subscription')->where('id',$post['asset_id'])->get();        foreach($subscription as $row)        {        DB::table('tbl_asset_subscription')->where('id',$row->id)->delete();        }        $orderId = array();        foreach($subscription as $row)        {        $order = DB::table('tbl_asset_order')->where('subscription_id',$row->id)->first();        $orderId[] = $order->id;        }        foreach($orderId as $row)        {        DB::table('tbl_asset_payment')->where('order_id',$row->id)->delete();        DB::table('tbl_asset_order')->where('id',$row->id)->delete();        }        DB::table('tbl_asset_versions')->where('asset_id',$post['asset_id'])->delete();        DB::table('tbl_assets')->where('id',$post['asset_id'])->delete();        // DB::table('tbl_asset_subscription')->where('asset_id',$post['asset_id'])->delete();        echo(json_encode(array("result" => true)));{    "message": "SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`piccoscript`.`tbl_asset_subscription`, CONSTRAINT `fk_tbl_asset_subscription_tbl_assets` FOREIGN KEY (`asset_id`) REFERENCES `tbl_assets` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) (SQL: delete from `tbl_assets` where `id` = 1)",    "exception": "Illuminate\\Database\\QueryException",    "file": "/var/www/html/piccoscript/vendor/laravel/framework/src/Illuminate/Database/Connection.php",    "line": 664,    "trace": [        {            "file": "/var/www/html/piccoscript/vendor/laravel/framework/src/Illuminate/Database/Connection.php",            "line": 624,            "function": "runQueryCallback",            "class": "Illuminate\\Database\\Connection",            "type": "->"        },

Answer 1

您可以暂时禁用外键约束

在 Laravel 中，您可以执行以下操作：

$orderId = array();

$subscription = DB::table('tbl_asset_subscription')->where('id',$post['asset_id'])->get(); 

DB::transaction(function() {

    DB::statement('SET FOREIGN_KEY_CHECKS=0');

    foreach($subscription as $row) {

        DB::table('tbl_asset_subscription')->where('id', $row->id)->delete();

    }

    foreach($subscription as $row) {

        $order = DB::table('tbl_asset_order')->where('subscription_id', $row->id)->first();

        $orderId[] = $order->id;

    }

    if($orderId) {

        DB::table('tbl_asset_payment')->whereIn('order_id', $orderId)->delete();

        DB::table('tbl_asset_order')->whereIn('id', $orderId )->delete();

    }

    DB::table('tbl_asset_versions')->where('asset_id', $post['asset_id'])->delete();

    DB::table('tbl_assets')->where('id', $post['asset_id'])->delete();

    DB::statement('SET FOREIGN_KEY_CHECKS=1');

});

echo(json_encode(array("result" => true)));

Answer 2

$orderId 不是关联数组，因此出现错误。试试这个代码。

foreach($subscription as $row)

{

$order = DB::table('tbl_asset_order')->where('subscription_id',$row->id)->first();

$orderId[] = array(

        'id' => $order->id

    );

}

foreach($orderId as $row)

{

DB::table('tbl_asset_payment')->where('order_id',$row->id)->delete();

DB::table('tbl_asset_order')->where('id',$row->id)->delete();

}

DB::table('tbl_asset_versions')->where('asset_id',$post['asset_id'])->delete();

DB::table('tbl_assets')->where('id',$post['asset_id'])->delete();

// DB::table('tbl_asset_subscription')->where('asset_id',$post['asset_id'])->delete();

echo(json_encode(array("result" => true)));