Many_Many Relation On A Non Pk Foreign Key

Hello,

I’m trying to define a relation with a N…N table, but the problem is that the middle table doesnt use the PK of a table:

Exemple

Table user :


id (identity)

code AK/FK   <-- this code is only generated when an user is validated...

...

Table commission :


id (identity)

name

Table commissionfilter :


idcommission

codeperson

User Model relations (disabled to fix it) :


'commissions' => array(self::MANY_MANY, 'Commission', 'commissionfilter(codeperson, idcommission)'),



Is there a way to force (like HAS_MANY or HAS_ONE relations) the key used in the user table

I tried to make a manual relation with :




	public $_commissions;

...

	/**

	 * Complex definition of "commissions" relation via commissionfilter

	 */

	public function getCommissions()

	{

		$criteria = new CDbCriteria;


		$code = $this->code;

		if (!empty($code)) {

			$criteria->join =

				'LEFT JOIN commissionfilter CF ON CF.codeperson= '.$code;

			$criteria->condition = 't.id = CF.idcommission';

		} else {

			$criteria->condition = 't.id = 0';

		}


		return Commission::model()->findAll($criteria);

	}

but i can’t use User::model()->with(‘commissions’)->findByPk($id); in this case…

>> Relation "commissions" is not defined in active record class "User".

but hmmm, it seems to work without the with()… so, is there a way to make a USER defined relation to keep the optional loading system ?

Edit: in fact the relation seems to works now… confused

Hello Tanguy, welcome to the forum.

Did you try this?




'commissions' => array(self::MANY_MANY, 'Commission', 'commissionfilter(array(codeperson=>code), idcommission)'),



It’s not very clear from the description of the guide if custom FK->PK pair is also supported in MANY_MANY relation or not. In fact I don’t know myself. But I think it’s worth trying.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship