Yii Framework Forum: How To Delete Recrods From Multiple Tables At Once - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How To Delete Recrods From Multiple Tables At Once Using Relational Active Record to delete data from multiple tables Rate Topic: -----

#1 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 08:59 AM

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

#2 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 09:05 AM

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

0

#3 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 09:09 AM

View PostKeith, 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.
0

#4 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 09:13 AM

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

#5 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 09:17 AM

View PostKeith, 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.
0

#6 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 09:31 AM

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

#7 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 09:39 AM

View PostKeith, 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.
0

#8 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 09:49 AM

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

#9 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 09:58 AM

View PostKeith, 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!
0

#10 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 10:13 AM

This might get you on the right track.
0

#11 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 10:17 AM

View PostKeith, on 23 November 2012 - 10:13 AM, said:



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

#12 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,561
  • Joined: 04-March 10
  • Location:UK

Posted 23 November 2012 - 10:22 AM

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

#13 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 10:40 AM

View PostKeith, 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!
0

#14 User is offline   robregonm 

  • Expert Yii Developer
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 594
  • Joined: 30-July 09
  • Location:Colombia

Posted 23 November 2012 - 11:57 AM

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.
Ricardo Obregón

YiiFramework en Español - http://yiiframework.co/ - http://yiiframeworkenespanol.org/ - Yii Code Generator for Bootstrap
http://obregon.co/ - https://1server.co/
PHP 5.5+, nginx 1.7, MySQL(MariaDB & PerconaDB), PostgreSQL 9, Yii 2, CanJS
Follow me: @robregonm & @obregonco & @1ServerCo.
0

#15 User is offline   alejofonseca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 60
  • Joined: 04-October 11
  • Location:Colombia

Posted 23 November 2012 - 11:59 AM

View Postrobregonm, 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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users