Validation rule for unique MANY_MANY combinations

Hello,

I’m currently writing a solution for users to create combinations of items, my database schema looks like this :

tbl_combination (id, …)

tbl_combination_item (id, combination_id, font_id)

tbl_item (id, …)

I created 2 models, Combination and Item, configured with MANY_MANY relations, and I use the activerecord-relation extension to easily save the new combinations. Everything works fine except that I’d like to verify that a combination doesn’t already exists before saving it.

Basically this means that there shouldn’t exist any combination with the same items and the same number of items.

Let’s say the user is trying to create a new combination with the items id 4 and 6.

The SQL to check if this combination already exists would be like




SELECT combination_id 

FROM tbl_combination_item 

WHERE item_id IN (4,6) 

GROUP BY combination_id 

HAVING COUNT(id) = 2;

What’s the “Yii way” to do this ? I have created a new validation rule with the raw SQL, but I’d like to know if this could be done via the built-in “unique” rule, or maybe if the SQL can be built using some helpers ?

I’ve searched the forum, doc and wiki, but I haven’t found this explained. I’m pretty new to Yii.

Thanks !

fatz

I’ve done this so far, it works but maybe not ideal.

Here is my validator :




	public function uniqueCombination($attribute, $params)

	{

		$sql = "SELECT combination_id FROM tbl_combination_item WHERE item_id IN (";

		foreach ($this->$attribute as $index => $itemId){

			$sql .=":item_".$index;

			if ($index != (count($this->$attribute) -1)){

				$sql .= ", ";

			}

		};

		$sql .= ") GROUP BY combination_id HAVING COUNT(id) = :itemCount";

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

		foreach ($this->$attribute as $index => $itemId){

			$command->bindValue(':item_'.$index , $itemId);

		}

		$command->bindParam(':itemCount', count($this->$attribute));

		if ($command->queryAll())

		{

			$this->addError('items', 'This combination already exists.');

		};

	}



And I have this rule in my Combination model :




array('items', 'uniqueCombination'),



Any comment appreciated to improve this, especially I believe the looping part coupled with the bindValue… is not optimal ;)

Cheers,

fatz