How To Delete Recrods From Multiple Tables At Once

meklop · November 23, 2012, 1:59pm

Hi there,

I’m stuck with an issue about relational active record: I have a couple of tables, Post and Author, that are defined as follows:

Post

id INT

author_id INT

post name VARCHAR(40)

Author

id INT

name VARCHAR(40)

last name VARCHAR(40)

The relations have been sorted out in each model. The question is, how to perform a "delete" action on both tables using the relations?

This is the code I’ve got but doesn’t work:




Post::model()->with('author_relation')->deleteAll(

    array(

        'condition'=>'t.author_id=5',

    )

);

It is supposed to delete the Author record with id 5 from the Author table AND the Post record from table Post where author_id = 5.

Thanks.

Keith · November 23, 2012, 2:05pm

That’s not supported in the current version of Yii. In this case, you should use two separate statements to issue the delete.

Alternative options if the deletion of Author should always occur upon the deletion of Post are:

Override afterDelete() in Post to ensure the Author table is automatically cleaned up.
Set a foreign key in the database with ON DELETE CASCADE, so that the database engine handles the cleanup itself (assuming you’re using InnoDB).

meklop · November 23, 2012, 2:09pm

Thanks a lot Keith. I’ll go with the afterDelete() option.

Keith · November 23, 2012, 2:13pm

In that case, you may want to consider wrapping your deletion of the Post record in a transaction, so you can roll back if the Author deletion fails. That’s again assuming that you’re using InnoDB and that data integrity is important.

meklop · November 23, 2012, 2:17pm

I’m not using InnoDB, unfortunately. But will consider the wrapping into a transaction. Thanks a lot for your advice.

Keith · November 23, 2012, 2:31pm

If it’s MyISAM, I’m afraid transactions aren’t supported. There’s probably no harm in wrapping in a transaction, but it won’t actually do anything to prevent data inconsistency.

meklop · November 23, 2012, 2:39pm

I’ve got this code in a controller. Are you saying it doesn’t make a lot of sense to have it as it won’t work as it should?


foreach($row as $value){

    $transaction = Yii::app()->db->beginTransaction();

    try{

	$sql = "INSERT INTO some_table";

	$sql .= "VALUES ('$value[0]','$value[2]');";

	Yii::app()->db->createCommand($sql)->execute();

	$transaction->commit();

    }

    catch(Exception $e){

	$transaction->rollback();

    }

}

If I don’t use a transaction here, i’ll get an error.

Keith · November 23, 2012, 2:49pm

If you’re using MyISAM (can you confirm this?), then transactions will not do anything as they are not supported by the engine. If you have the option, then you should consider switching to InnoDB.

meklop · November 23, 2012, 2:58pm

Yes, I’m using MyISAM. I’ll consider switching to InnoDB.

Have you got a link to a tutorial where I could see how transactions work?

Thanks a lot for your help!

Keith · November 23, 2012, 3:13pm

This might get you on the right track.

meklop · November 23, 2012, 3:17pm

I’ve read those articles already, what I didn’t know was that transactions only do the proper job with InnoDB. A shame that those articles don’t mention it.

Regards.

Keith · November 23, 2012, 3:22pm

It’s a limitation of the database engine unfortunately. I imagine the people who wrote the documentation didn’t want to have to maintain a list of the database engines that don’t support transactions.

meklop · November 23, 2012, 3:40pm

Agree. In this case I’ll have to evaluate the chances of switching to InnoDB.

Thanks a lot!

robregonm · November 23, 2012, 4:57pm

You can solve it adding "ON DELETE CASCADE" when creating the table (at least for MySQL, PostgreSQL, Firebird)

You can change it whenever you want using phpmyadmin or similar.

Best regards.

meklop · November 23, 2012, 4:59pm

Thanks a lot for your reply. “ON DELETE CASCADE” only works within InnoDB type of tables I use MyISAM.

Best.