Laravel how to delete a row from multiple tables in one query

Question

In my question answers project, the admin has a functionality to view all questions and delete each one if he wishes. but one question id has 5 related tables. that is , each question id is present in 5 tables in db and when admin deletes one question i have to delete all the entries from these 5 tables based on this question id.

in my controller

DB::table('user_questions')->where('question_id','=',$question_id)->delete();  
DB::table('masters_answers')->where('question_id','=',$question_id)->delete();  
DB::table('masters_questions')->where('question_id','=',$question_id)->delete(); 
DB::table('question_to_category')->where('question_id','=',$question_id)->delete(); 
DB::table('question_to_tags')->where('question_id','=',$question_id)->delete(); 
return Redirect::action('AdminController@anyShowQuestions', array());

The above code will works but is there any way for do the same procedure in one db query in laravel.? i had referred this post but cannot find a solution for mine. it will be so helpful if anyone suggest a right way??

Since the records are all related you could set up foreign key constraints and set `ON DELETE` to `CASCADE` — lukasgeiter, Nov 19 '14 at 12:38

score 0 · Answer 1 · edited May 23 '17 at 12:12

You can set your foreign keys properly on this related tables. Add onDelete('cascade') when you are creating keys in migrations and if everything is ok, then, when you delete the question it would automatically delete the related items too.

OR

Use the DB::statementexpression with the respective SQL query.

Example:

DB::statement('update foo set bar=2');

You would need to solve your task in plain SQL, and then put the resulting query in the DB::statement and viola.

To help you with the query, you can read this article or this StackOverflow post

score 0 · Answer 2 · answered Nov 19 '14 at 16:20

Setting foreign keys on your tables would be the cleanest solution

Alternatively, if you can't use foreign keys, you can use Model Events.

Each of your models fires several events, allowing you to hook into various points in the model's lifecycle, including the delete event. Use something like this in your model:

class User extends Eloquent {

    public static function boot()
    {
        //execute the parent's boot method 
        parent::boot();

        //delete your related models here, for example
        static::deleted(function($user)
        {
            foreach($user->user_profiles as $profile)
            {
                $profile->delete();
            } 
        }

    }

}

Now every time a User gets deleted, all of the atatched User_Profile weill be deleted as well.

Laravel how to delete a row from multiple tables in one query

2 Answers2