Disambiguating columns when an AR relation refers to the same table

Archives Yii 1.0.x Discussions
1

Hi,

I’ve problems managing AR relations involving the same table, in particular using hierarchies and the single table inheritance pattern.

Consider, for example, a table Person with three columns: id, marriedWithId, name, gender.

I have to create two AR classes, Male and Female, that are mapped both to Person table.

I have to redefine default scope of both classes (example for class Female):




public function defaultScope()

{

   return array('condition' => $this->tableName().".gender='female'");

}

This allows things like Female::model()->findAll() to effectively return just rows with the column "gender" set to "female".

Then consider the relation "wife" defined in the Male class:


'wife' => array(self::BELONGS_TO, 'Female', 'marriedWithId')

Now, I can load a Male named ‘Homer’


$homer = Male::model()->findByAttribute(array('name' => 'Homer'))

Now, if I try to load homer’s wife, I will get in every case no results:


$marge = $homer->wife;

$marge is always null!!!!

This is because during the query there will be a join between the same table, Person, and the condition gender=‘female’ is thus applied on both left and right side of the join, because no disambiguation on columns name is apparently possible.

Is there a way to resolve this matter!?!?!?

Thanks everybody.

Regards,

Mario.

2

could you show us your relation implementation?

3

These are my effective AR classes (obviously just important portions of them):




class Media extends CActiveRecord

{

	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'Media';

	}

	

	/**

	 *

	 */

	public function defaultScope()

	{

		if(__CLASS__ != get_class($this))

	    	return array('condition' => $this->tableName().".type='".$this->getType()."'");

	    else

	    	return array();

	}

	

	/**

	 *

	 */

	public function beforeSave()

	{

		parent::beforeSave();

		if($this->isNewRecord)

			$this->id = Identifiers::getNewId(__CLASS__);

		

		$this->type = $this->getType();


		return true;

	}

	


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		return array(

		);

	}

	

	protected function instantiate($attributes)

	{

		switch($attributes['type'])

		{

			case Media::TYPE_IMAGE:

				return new Image(null);

				break;

			case Media::TYPE_PRESENTATION:

				return new Presentation(null);

				break;

			case Media::TYPE_VIDEO:

				return new Video(null);

				break;

		}

		

		return parent::instantiate($attributes);

	}

}





class Presentation extends Media

{

	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		return array(

			'cuepoints' => array(self::HAS_MANY, 'Cuepoint', 'presentationId'),

			'video' => array(self::BELONGS_TO, 'Video', 'videoId'),

			'linkedMedias' => array(self::MANY_MANY, 'Media', 'linkPresRes(presId, resId)')

		);

	}

	

	public function getType()

	{

		return Media::TYPE_PRESENTATION;	

	}

}




class Video extends Media

{

	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		return array(

			'thumbnail' => array(self::BELONGS_TO, 'Image', 'thumbnailId')

		);

	}


	public function getType()

	{

		return Media::TYPE_VIDEO;	

	}

}

I’ve problems getting the “video” relation of the class Presentation.

4

Hi.

I had a similar problem and I solved it using a VIEW.

If I had your table "Person" I would create View with name e.g. View_Person that would be en exact copy of the table. For both the table and the view I would create Model. In the "table model" I would define relation to the "view model". Because content of the view is the same as content of the table, this relation would virtually point from table to itself.

But Views have no PKs, so I don’t know if this could help you.