Yii Framework Forum: Delete Many-Many Related Models - Yii Framework Forum

Jump to content

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

Delete Many-Many Related Models Rate Topic: -----

#1 User is offline   colix 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 04-February 12
  • Location:Moscow, Russia

  Posted 15 October 2012 - 05:37 AM

Hi there.

In my app I have Category, Post and PostCategory model.

Category:
	public function relations()
	{
		return array(
			'products' => array(self::MANY_MANY, 'Product', 'tbl_product_category(category_id, product_id)'),
		);
	}

Product:
	public function relations()
	{
		return array(
			'categories' => array(self::MANY_MANY, 'Category', 'tbl_product_category(product_id, category_id)'),
		);
	}

ProductCategory:
	public function relations()
	{
		return array(
			'category' => array(self::BELONGS_TO, 'Category', 'category_id'),
			'product' => array(self::BELONGS_TO, 'Product', 'product_id'),
		);
	}


And when deleting one category I want to delete all products in it. This is how I'm trying to do it:
		$transaction=Category::model()->dbConnection->beginTransaction();
		try
		{
			$criteria=new CDbCriteria;
			$criteria->condition='category_id=:category_id';
			$criteria->params=array(':category_id'=>$id);
			ProductCategory::model()->deleteAll($criteria);			
		
			$categories = Category::model()->with('products')->findByPk($id);
			$categories->deleteAll();
						
			$transaction->commit();
		}
		catch(Exception $e)
		{
			$transaction->rollback();
			throw $e;
		}


And I always get exception:
Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`sale-monster`.`tbl_product_category`, CONSTRAINT `tbl_product_category_ibfk_6` FOREIGN KEY (`category_id`) REFERENCES `tbl_category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION). The SQL statement executed was: DELETE FROM `tbl_category`
What am I doing wrong? Should I use "on delete cascade" option to perform this deletion, if so, please help me to do it.

Thanks a lot in advance, Max.
0

#2 User is offline   ragua 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 135
  • Joined: 23-January 12
  • Location:Paris - France

Posted 15 October 2012 - 05:52 AM

Hi,
as this is a database message (not a Yii message), you should use the cascading delete of your db.
0

#3 User is offline   colix 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 04-February 12
  • Location:Moscow, Russia

Posted 15 October 2012 - 07:54 AM

View Postragua, on 15 October 2012 - 05:52 AM, said:

Hi,
as this is a database message (not a Yii message), you should use the cascading delete of your db.

And where exactly I shuld set on cascade delete?
Posted Image

I tried different combinations, like set on delete on product_id, category_id and even both and nothing works properly :(
0

#4 User is offline   ORey 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,699
  • Joined: 20-April 09
  • Location:Moscow, Russia

Posted 15 October 2012 - 08:03 AM

You can use

FOREIGN KEY ... ON DELETE CASCADE (InnoDB only) like Ragua has already mentioned, <--- this is the simplest

or Yii triggers, like beforeDelete (notice that deleteAll will not run the triggers),

or consequent delete (delete related then the record itself),

or drop foreign key constraint and do all the cleaning stuff when it's convenient (for example, by crontab once a day),

or something else.

The choice is yours, and it depends on your task.
God is real unless declared as integer
0

#5 User is offline   colix 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 04-February 12
  • Location:Moscow, Russia

Posted 15 October 2012 - 12:33 PM

View PostORey, on 15 October 2012 - 08:03 AM, said:

You can use

FOREIGN KEY ... ON DELETE CASCADE (InnoDB only) like Ragua has already mentioned, <--- this is the simplest

or Yii triggers, like beforeDelete (notice that deleteAll will not run the triggers),

or consequent delete (delete related then the record itself),

or drop foreign key constraint and do all the cleaning stuff when it's convenient (for example, by crontab once a day),

or something else.

The choice is yours, and it depends on your task.

Quote

FOREIGN KEY ... ON DELETE CASCADE (InnoDB only) like Ragua has already mentioned

Just don't know where to activate ON CASCADE DELETE to get it work

Quote

or Yii triggers, like beforeDelete (notice that deleteAll will not run the triggers),or Yii triggers, like beforeDelete (notice that deleteAll will not run the triggers)

It's an option, yes.

Quote

or consequent delete (delete related then the record itself)

Tried, jsut not working for me

Quote

or drop foreign key constraint and do all the cleaning stuff when it's convenient (for example, by crontab once a day)

Interesting option, thanks a lot for all. Спасибо)
0

#6 User is offline   alirz23 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,025
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 17 October 2012 - 01:04 AM

if thats not working as Orey suggested you can use the AR hooks
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