Best Way Of Creating Unlimited Parent -> Child Relationship

I was wondering what is the best way of achieving this with Yii.

Example image: tinyurl.com/mhw6j2u

And let’s say I wanted to get all Parents with all of their children. How should I design the database and the relationships with Yii?

look at this extension :nestedset-behavior

Thanks… But how deep down in the tree can I go with this?

Is this really "unlimited" ?

Yes, the method above is unlimited.

Another simpler method is to use a self-referencing table.

Basically, you have a foreign key that references the primary key of the same table.


id <--------┐

            |    

parent_id >-┘

parent_id should be nullable!

So Ann has a daughter Mary and Mary has 2 sons, Robert and Andrew:


id: 1

parent_id: NULL

name: Ann


id: 2

parent_id: 1

name: Mary


id: 3

parent_id: 2

name: Robert


id: 4

parent_id: 2

name: Andrew

Thanks.

This was acctually my first suggestion before I heard of Nested Sets. But according to this: http://www.evanpeter…ested-sets.html

it is not very good to use "parent_id"-method for storing trees if you have a lot of users since every depth would require an additional query to load its children. What is your opinion?

Also, do you know if the extension "nestedset-behavior" can go as deep as possible without messing up the tree?

I tried using the schema that comes with the extension ([color="#333333"][font="Consolas,"]extensions/yiiext/behaviors/trees/schema.sql)[/font][/color]…

I also added the "title" column which was not included :S

I then generated the Controller, Model & CRUD with Gii and added this to the newly created Model: Category.php





	public function behaviors()

	{

	    return array(

	        'nestedSetBehavior'=>array(

	            'class'=>'ext.yiiext.behaviors.model.trees.NestedSetBehavior',

	            'leftAttribute'=>'lft',

	            'rightAttribute'=>'rgt',

	            'levelAttribute'=>'level',

	        ),

	    );

	}

I Also placed the NestedSetBehavior.php in protected/extensions/yiiext/behaviors/model/trees/

And then I added this to the controller indexAction:




$root=new Category;

$root->title='Mobile Phones';

$root->saveNode();



But it is not saving anything in the database…

What could possibly be wrong?

I have found that the method "save" found in NestedSetBehavior.php has this code:




if($runValidation && !$owner->validate($attributes))

    return false;



…which is failing at my end. [size=“2”]!$owner->validate($attributes) is returning false… And I don’t know why…[/size]

Muhaha! I have found the solution…

The problem was in the model Category.

I changed the validation rules so that ‘lft’, ‘rgt’ and ‘level’ are not required. Since these are automatically added by NestedSetBehavior.

Before change:




	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('lft, rgt, level', 'required'),

			array('level', 'numerical', 'integerOnly'=>true),

			array('root, lft, rgt', 'length', 'max'=>10),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, root, lft, rgt, level', 'safe', 'on'=>'search'),

		);

	}

After Change:




	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			//array('lft, rgt, level', 'required'),

			array('level', 'numerical', 'integerOnly'=>true),

			array('root, lft, rgt', 'length', 'max'=>10),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, root, lft, rgt, level', 'safe', 'on'=>'search'),

		);

	}

It’s working perfectly now! :D