How to implement a self join

Hi,

Completely new to Yii, so pardon my ignorance…

As a first project I try to implement an app for some genealogy research I’m doing.

I have a Person table, simply with an id, a name, a father_id and a mother_id.

The father_id points to the id of the Person that is the father. Same for the mother.

Using this structure, the Person table basically has two self-joins. One for the father-child relation and one for the mother-child relation.

I created three model classes: Person, Father (subclass of Person) and Mother (also subclass of Person). Both Father and Mother model have no added behaviour or attributes.

Following the tutorial, in the Person model I defined relations as follows:




'father'=>array(self::HAS_ONE, 'Father', 'father_id'),

'mother'=>array(self::HAS_ONE, 'Mother', 'mother_id'),



This doesn’t work as expected, as it appears that the relation is resolved fromt the right side of the relation expecting the foreign key with the father/mother side while it is actually on the child side.

I’m obviously missing the point here. Any help very much appreciated!

There are some workarounds but can we see the resulting SQL?

A workaround could be (very simple example):




// write two variables to hold the property values to the model Person

protected $_mother;

protected $father;


// write two properties

public function getFather(){

  if( null === $this->_father && $this->father_id != 0 ) // do not know default values i just use 0

  {

     $this->_father = Father::model()->findByPk($this->father_id);

  }

  return $this->_father; 

}


// same for mother... 

.

.

.

// now we can access the father like if it was lazy loading:

// be careful if it is null, this is just as an example

$this->father->attribute... 




If you used a self::BELONGS_TO relationship then it would work as expected. Normally I’d say speak out the relationship but in this case it works for either one:

Person HAS ONE Father

Person BELONGS TO Father

If you considered it the other way around however it might have been clearer, if you reverse the above:

Father BELONGS TO Person

Father HAS MANY Person

Failing all that just remember that the BELONGS_TO relationship has the FK in the current model and the HAS_ONE relationship has the FK in the relationship model.

Thanks for your answers. Greatly appreciated.

@Say_Ten

I initially defined the relation as a BELONGS_TO relation. However that had an unwanted side effect that when I entered a new person and connected it to an existing Father person, the new person was inserted so many times as its father was already related to other persons as a father. So, for a person who has already three children, adding another new child would result in an insertion of 4 instances of that new person (3 for each existing child plus the new).

I was not able to explain that behaviour and figured the chosen BELONGS_TO was the wrong relation type.

@Antonio Ramirez

Great workaround. I’ll give it try. How can I make the resulting SQL visible? I tried CakePHP as well and they have this nice feature of posting the SQL while developing. I was put off Cake due to the way the view layer communicates with the controllers by just sending array of data in stead of proper objects.

Check this http://www.yiiframework.com/wiki/58/sql-logging-and-profiling-in-firebug-yii-1-1

or just enable CWebLogRoute if you wish to have it displayed on the page.

Also on the forum you find lots of info about it.