Yii Framework Forum: please test my AR Enhancement: automatically sync MANY_MANY table when calling save() - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

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

#1 User is offline   thyseus 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 300
  • Joined: 18-April 09
  • Location:Leipzig, Germany

Posted 26 January 2010 - 08:32 AM

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);
  }


0

#2 User is offline   thyseus 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 300
  • Joined: 18-April 09
  • Location:Leipzig, Germany

Posted 28 January 2010 - 12:26 PM

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

http://www.yiiframew...ncedarbehavior/

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

#3 User is offline   Rangel Reale 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 68
  • Joined: 14-January 10

Posted 28 January 2010 - 01:30 PM

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;
	}
}

0

#4 User is offline   Rangel Reale 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 68
  • Joined: 14-January 10

Posted 28 January 2010 - 01:34 PM

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)'),
		);
	}

0

#5 User is offline   thyseus 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 300
  • Joined: 18-April 09
  • Location:Leipzig, Germany

Posted 29 January 2010 - 06:30 PM

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

#6 User is offline   jerry2801 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 504
  • Joined: 18-November 09

Posted 30 January 2010 - 06:52 AM

Back you up~
0

#7 User is offline   UX.InfoGate 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 10-February 10

Posted 10 February 2010 - 05:09 PM

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();
        }
    }
}

0

#8 User is offline   intel352 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 196
  • Joined: 05-February 10
  • Location:Southport, NC

Posted 08 March 2010 - 09:34 AM

UX.InfoGate, I like your version of the behavior, seems much more concise :-)
Haven't compared to Thyseus' release in actual application, though, but I hope to do so soon!

View PostUX.InfoGate, on 10 February 2010 - 05:09 PM, said:

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();
        }
    }
}


Need live Yii support? - Join the #yii IRC channel on Freenode!
0

#9 User is offline   thyseus 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 300
  • Joined: 18-April 09
  • Location:Leipzig, Germany

Posted 08 March 2010 - 09:40 AM

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

#10 User is offline   intel352 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 196
  • Joined: 05-February 10
  • Location:Southport, NC

Posted 08 March 2010 - 12:36 PM

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?)
Need live Yii support? - Join the #yii IRC channel on Freenode!
0

#11 User is offline   UX.InfoGate 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 10-February 10

Posted 19 March 2010 - 09:58 AM

thyseus said:

If you like, i will use this as a codebase for future Versions of the AdvancedArbehavior

That's will be great!!!

View Postintel352, on 08 March 2010 - 12:36 PM, said:

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


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

#12 User is offline   thyseus 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 300
  • Joined: 18-April 09
  • Location:Leipzig, Germany

Posted 14 April 2010 - 01:39 AM

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.c...dArBehavior.php
0

#13 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 15 April 2010 - 11:17 PM

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

#14 User is offline   Fab 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 26-April 10

Posted 26 April 2010 - 02:53 AM

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

#15 User is offline   srigi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-April 10

Posted 29 April 2010 - 03:14 AM

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();

1

#16 User is offline   cma 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 28-July 09

Posted 29 April 2010 - 08:57 AM

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 )' ),

0

#17 User is offline   srigi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-April 10

Posted 05 May 2010 - 06:23 AM

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

#18 User is offline   aleksdj 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 05-April 10

Posted 06 May 2010 - 05:07 PM

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

#19 User is offline   krzysiek 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 07-May 10

Posted 07 May 2010 - 04:16 AM

View Postsrigi, on 05 May 2010 - 06:23 AM, said:

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.


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.c...ser/components/
0

#20 User is offline   Paul Apostol 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 61
  • Joined: 25-March 09

Posted 16 May 2010 - 08:15 AM

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
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • 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