please test my AR Enhancement: automatically sync MANY_MANY table when calling save()

Hi everyone,

let´s think about pimping up our relation ActiveRecord up even further.

Assuming the following MANY-to-MANY relation:




Post has:

 'categories'=>array(self::MANY_MANY, 'Category',

                'tbl_post_category(post_id, category_id)'),


Category has:

 'posts'=>array(self::MANY_MANY, 'Post',

                'tbl_post_category(category_d, post_id)'),




Now we can do this:




$post = new Post();

$post->categories = Category::model()->findAll();

$post->save();



This will save our new Post in the table Post, and in addition to this it updates our N:M-Table with every Category available in the System.

We can also do this:




$category = new Category();

$category->posts = array(5, 6, 7, 10);

$caregory->save();



This saves our new Category and adds the Post with the primary key of 5, 6, 7 and 10 in the Databases. 5 Queries will be performed here, one for the Category-Model and four for the N:M-Table tbl_post_category.

We can also pass a single object:




$category = new Category();

$category->posts = Post::model()->findByPk(12);

$category->save();



or a single integer:




$post = new Post();

$post->categories = 7;

$post->save();



This is my basic implementation of this behavior. It would like to hear what you low-level Yii Cracks think about it :)

Place this anywhere in your Model file:




 public function afterSave() {

    parent::afterSave();


    Yii::trace('writing MANY_MANY data for '.get_class($this),'system.db.ar.CActiveRecord');


    foreach($this->relations() as $key => $relation)

    {

      if($relation['0'] == self::MANY_MANY) // relationType

      {

        if(isset($this->$key))

        {

          if(is_object($this->$key) || is_numeric($this->$key))

          {

            $query = $this->makeManyManyCommand(

              $relation[2],

              $this->{$this->tableSchema->primaryKey},

              (is_object($this->$key))

              ?  $this->$key->{$this->$key->tableSchema->primaryKey}

              : $this->{$key});

            $this->insertManyManyEntry($query);

          }

          else if (is_array($this->$key) && $this->$key != array())

          {

            foreach($this->$key as $foreignobject)

            {

              $query = $this->makeManyManyCommand(

                $relation[2],

                $this->{$this->tableSchema->primaryKey},

                (is_object($foreignobject))

                ? $foreignobject->{$foreignobject->tableSchema->primaryKey}

                : $foreignobject);

                $this->insertManyManyEntry($query);

            }

          }

        }

      }

    }

  }


  public function insertManyManyEntry($query) {

    if(!Yii::app()->db->createCommand($query)->execute())

      throw new CException(Yii::t('yii','an Error occured while trying to update MANY_MANY relation table'));


  }

  public function makeManyManyCommand($model, $rel, $foreignrel) {

    return sprintf("insert into %s values ('%s', '%s')", $model, $rel, $foreignrel);

  }




Just want to mention that i have made an behavior with some additions from this concept, that deprecates the code shown above…

http://www.yiiframework.com/extension/cadvancedarbehavior/

Discussion for this Extension should be made here - anyone have any Feature Request or Bug Reports for this so far - or positive Reviews :)

Good extension, I modified it so it works for updates too (it deletes the previous records before inserting them).

(I removed the comments for the post)




class CAdvancedArBehavior extends CActiveRecordBehavior

{

	public function afterSave($on) {

		$this->writeManyManyTables();

		return TRUE;

	}


  public function writeHasMany() {


	}


	public function writeManyManyTables() {

		Yii::trace('writing MANY_MANY data for '.get_class($this->owner),'system.db.ar.CActiveRecord');


		foreach($this->owner->relations() as $key => $relation)

		{

			if($relation['0'] == CActiveRecord::MANY_MANY) // ['0'] equals relationType

			{

				if(isset($this->owner->$key))

				{

					if(is_object($this->owner->$key) || is_numeric($this->owner->$key))

					{

						$this->executeManyManyEntry($this->makeManyManyDeleteCommand(

							$relation[2],

							$this->owner->{$this->owner->tableSchema->primaryKey}));

						$query = $this->owner->makeManyManyCommand(

							$relation[2],

							$this->owner->{$this->owner->tableSchema->primaryKey},

							(is_object($this->owner->$key))

							?  $this->owner->$key->{$this->owner->$key->tableSchema->primaryKey}

							: $this->owner->{$key});

						$this->owner->executeManyManyEntry($query);

					}

					else if (is_array($this->owner->$key) && $this->owner->$key != array())

					{

						$this->executeManyManyEntry($this->makeManyManyDeleteCommand(

							$relation[2],

							$this->owner->{$this->owner->tableSchema->primaryKey}));

						foreach($this->owner->$key as $foreignobject)

						{

							$query = $this->makeManyManyCommand(

								$relation[2],

								$this->owner->{$this->owner->tableSchema->primaryKey},

								(is_object($foreignobject))

								? $foreignobject->{$foreignobject->tableSchema->primaryKey}

								: $foreignobject);

							$this->executeManyManyEntry($query);

						}

					}

				}

			}

		}

	}


	public function executeManyManyEntry($query) {

		if(!Yii::app()->db->createCommand($query)->execute())

			throw new CException(Yii::t('yii','an Error occured while trying to update MANY_MANY relation table'));


	}

	public function makeManyManyDeleteCommand($model, $rel) {

		return sprintf("delete from %s where %s = '%s'", $this->getManyManyTable($model), $this->owner->tableSchema->primaryKey, $rel);

	}

	public function makeManyManyCommand($model, $rel, $foreignrel) {

		return sprintf("insert into %s values ('%s', '%s')", $model, $rel, $foreignrel);

	}

	public function getManyManyTable($model) {

		if (($ps=strpos($model, '('))!==FALSE)

		{

			return substr($model, 0, $ps);

		}

		else

			return $model;

	}

}



I also made a change to CHtml so CHtml::activeCheckboxList works where receiving an array of relation objects. Just pass a parameter "attributeitem" into htmlOptions with the attribute name of the objects.

Using these 2 codes I was able to make a checkboxlist onto a many-many table without any code changes.




	public static function activeCheckBoxList($model,$attribute,$data,$htmlOptions=array())

	{

		self::resolveNameID($model,$attribute,$htmlOptions);

		if(is_array($model->$attribute) && isset($htmlOptions['attributeitem']) && $htmlOptions['attributeitem'])

		{

			$selection=array();

			foreach ($model->$attribute as $a)

				$selection[]=$a->$htmlOptions['attributeitem'];


		} else

			$selection=$model->$attribute;

		if($model->hasErrors($attribute))

			self::addErrorCss($htmlOptions);

		$name=$htmlOptions['name'];

		unset($htmlOptions['name']);


		return self::hiddenField($name,'',array('id'=>self::ID_PREFIX.$htmlOptions['id']))

			. self::checkBoxList($name,$selection,$data,$htmlOptions);

	}







echo $form->checkBoxList($model,'operations',CHtml::listData(WOBJOperation::model()->findAll(array('order'=>'ds_operation_ope')), 'cd_operation_ope', 'ds_operation_ope'), array('attributeitem'=>'cd_operation_ope'));






	public function relations()

	{

		return array(

			'operations'=>array(self::MANY_MANY, 'WOBJOperation', '{{wobj_admin_group_operation}}(cd_admingroup_adg, cd_operation_ope)'),

		);

	}



Thank you for your contribution !!

I have tidied up the Code, merged your new Feature, fixed a small Bug in your Code, and released Version 0.2 of this Extension.

Here’s some info about the fixed problem:

The fields identifying both Tables in the MANY_MANY Table doesn’t necessarily need to be called like the primary key of the Tables. That’s why you first have to resolve the name of the field from the relation[3] like this:




<?php

  public function makeManyManyDeleteCommand($model, $rel) {

    return sprintf("delete ignore from %s where %s = '%s'", $this->getManyManyTable($model), $this->getRelationNameForDeletion($model), $rel);

  }


  public function getRelationNameForDeletion($model) {

    preg_match('/\((.*),/',$model, $matches) ;

    return substr($matches[0], 1, strlen($matches[0]) - 2);

  }



It is also important to add IGNORE to the insert-Query, so he won’t raise an Exception when the deletion of the rows fails (which can easily happen when there is no entry in the MANY_MANY table yet).

Back you up~




class ManyToManyBehavior extends CActiveRecordBehavior

{

    public function afterSave($event)

    {

        parent::afterSave($event);

        $relations = $this->getRelations();

        array_walk($relations, array($this, 'save'));

    }

    

    public function beforeDelete($event)

    {

        parent::beforeDelete($event);

        $relations = $this->getRelations();

        array_walk($relations, array($this, 'delete'));

    }

    

    protected function getRelations()

    {

        $relations = array();

        foreach ($this->owner->relations() as $key => $relation) {

            if ($relation[0] == CActiveRecord::MANY_MANY &&

                $this->owner->hasRelated($key) &&

                $this->isScalarArray($this->owner->$key)

            ) {

                $info = array('abstractField' => $key,

                              'foreignTable'  => $relation[1]);

                

                if (preg_match('/^(.+)\((.+)\s*,\s*(.+)\)$/s', $relation[2], $pocks)) {

                    $info['m2mTable']        = $pocks[1];

                    $info['m2mThisField']    = $pocks[2];

                    $info['m2mForeignField'] = $pocks[3];

                }

                else {

                    $info['m2mTable']        = $relation[2];

                    $info['m2mThisField']    = $this->owner->tableSchema->primaryKey;

                    $info['m2mForeignField'] = CActiveRecord::model($relation[1])->tableSchema->primaryKey;

                }

                

                $relations[$key] = $info;

            }

        }

        return $relations;

    }

    

    protected function isScalarArray($a)

    {

        foreach ($a as $v) {

            if (!is_scalar($v)) {

                return false;

            }

        }

        

        return true;

    }

    

    protected function delete($relation)

    {

        $query = sprintf('DELETE FROM `%s` WHERE `%s` = :thisValue',

                         $relation['m2mTable'],

                         $relation['m2mThisField']);

        

        $command = Yii::app()->db->createCommand($query);

        $command->bindParam('thisValue', $this->owner->{$this->owner->tableSchema->primaryKey});

        $command->execute();

    }

    

    protected function save($relation)

    {

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

        

        try {

            $this->delete($relation);

            

            $query = sprintf('INSERT INTO `%s`(`%s`, `%s`) VALUES (:thisValue, :foreignValue)',

                             $relation['m2mTable'],

                             $relation['m2mThisField'],

                             $relation['m2mForeignField']);

            

            $command = Yii::app()->db->createCommand($query);

            $command->bindParam('thisValue', $this->owner->{$this->owner->tableSchema->primaryKey});

            

            foreach ($this->owner->{$relation['abstractField']} as $foreign_value) {

                $command->bindParam('foreignValue', $foreign_value);

                $command->execute();

            }

            

            $transaction->commit();

        }

        catch(Exception $e) {

            $transaction->rollBack();

        }

    }

}



UX.InfoGate, I like your version of the behavior, seems much more concise :slight_smile:

Haven’t compared to Thyseus’ release in actual application, though, but I hope to do so soon!

that’s great Code from UX.Infogate! Looks much more tidied than my Version. If you like, i will use this as a codebase for future Versions of the AdvancedArBehavior. Maybe this whole stuff get’s included in the Core ActiveRecord as soon as it gets [even more] more stable.

Thank you for your Great work!

Couple of notes on UX.Infogate’s version:

  1. It calls the parent afterSave and beforeDelete methods, but afaik, that is not needed in a behavior.

  2. This may not be an issue, but shouldn’t “behavior” be uppercased in the class definition? (extends …Behavior?)

That’s will be great!!!

  1. It was done because of possible future features of ActiveRecordBehavior class.

  2. It is issue of source code highlighter. If you get a look at HTML-code of this page you can find that "behavior" word displaying correctly.

PS. Sorry for my bad english ))

a long time nothing has changed in this extension. But now i found this forum thread that could make this extension even better. I am going to implement this ‘automatic database syncinc’ feature mentioned there in the CAdvancedArBehavior, make it toggleable (default to off) and will release 0.3 of this Version. Hopefully i will be able to merge UX.Infogate’s changes and code into this Version.

Is anyone interested to help develop this extension via svn? i ask because the ‘newest’ version is always available here

http://code.google.com/p/yii-user-management/source/browse/trunk/user/components/CAdvancedArBehavior.php

:D Oh looks nice will give it a try… hope MANY MANY support will be in the core though…

Hi!

Being a total newbie this might be a really stupid question but I’d still like to ask it.

I’ve downloaded this extension, placed it in the autoimport array (in main.php), added the MANY_MANY relation to my model, plus the bahaviors() method to decorate the class with this new behavior and now… I’m stuck.

I can get my view to display the related items just fine.

Code in view:


        <?php foreach ($data->categories as $category): ?>

        <?php echo CHtml::encode($category->name); ?>

        <?php endforeach; ?>



However… I don’t know how to get this relation populated with data.

I’ve downloaded another extension (Relation) which will display controls to add and remove my categories:

Code in form:




                <?php $this->widget('application.extensions.Relation', array(

                   'model' => 'Item',

                   'relation' => 'categories',

                   'fields' => 'name'

                   ));

                ?>



What do I have to do to actually save the stuff chosen on my form?

Committing the form will not save the values in my MANY_MANY table. And I’m not really expecting it to work out-of-the-box but I’d like to have a pointer, what I need to use. Where in the controller do I need to add something?

Thanks in advance!

I think I found a bug in thyseus’s CAdvancedArBehavior. I will use model situation from extension’s documentation with Categories and Post to explain problem.

Assume I have already some Categories an Posts in DB. If I fetch $singleCategory = Category::model()->find(CRITERIA) I get a Category object, where may be array of some Posts in $singleCategory->posts. Bug occurs when I clear all Posts from relation: $singleCategory->posts = array()

Expected behavior should be deletion all of Posts from Category. But this fails any no change is made. This is very strange to me, cos removing Posts from Category works fine (like this):




$temp = $singleCategory->posts;

unset($temp[0]);

$singleCategory->posts = $temp;

$singleCategory->save();



The order of primary keys in relations are importante. The first is always be related to the current model

for Post model:




 'categories'=>array( self::MANY_MANY, 'Category' , 'tbl_post_category( post_id , category_id )' ),



for Category model :




 'posts'=>array( self::MANY_MANY, 'Post' , 'tbl_post_category( category_id , post_id )' ),



Got everything setup in a right way. As I said, removing of related record works well util last is to be removed or an empty array of related records is assigned.

Plese, take som time investigating of issue, I think this is a serious bug.

I think that issue is in relation with (update on cascade or delete o cascade), but i’m not sure.

http://www.mysql-his…php?id=27&pag=7

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Hi srigi,

I had the same problem when assigning an empty array.

What solved the issue was updating CAdvancedArBehavior.php to the most recent version found here:

http://code.google.com/p/yii-user-management/source/browse/trunk/user/components/

Hi everybody,

Thanks for the AR behavior, good work.

An issue, at least on my app:


$post->categories = 7;

This doesn’t work, mainly because on line 149 is_object(int) is false. I’m using:


$post->categories = array(7);

and works perfect.

Regards,

Paul