[alejofonseca]

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]

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).

[alejofonseca]

Keith, on 23 November 2012 - 09:05 AM, said:

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).

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

[Keith]

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.

[alejofonseca]

Keith, on 23 November 2012 - 09:13 AM, said:

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.

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

[Keith]

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.

[alejofonseca]

Keith, on 23 November 2012 - 09:31 AM, said:

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.

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]

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.

[alejofonseca]

Keith, on 23 November 2012 - 09:49 AM, said:

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.

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]

This might get you on the right track.

[alejofonseca]

Keith, on 23 November 2012 - 10:13 AM, said:

This might get you on the right track.

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]

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.

[alejofonseca]

Keith, on 23 November 2012 - 10:22 AM, said:

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.

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

Thanks a lot!

[robregonm]

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.

[alejofonseca]

robregonm, on 23 November 2012 - 11:57 AM, said:

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.

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

Best.