Loading all tree relations at once

I have a model for category. Categories have their parents and subcategories – all of the same type (ergo – one model and database table for them).

I have defined a relation for subcategories and parents:


public function relations()

{

	return array(

		'subcategories' => array(self::HAS_MANY, 'Category', 'parentID', 'order'=>'position'),

		'parent' 	=> array(self::BELONGS_TO, 'Category', 'parentID'),

	);

}



When I want to load a tree of all categories, I can recursively go through all ‘subcategories’ relation but it means a lot of SELECT queries ergo a longer time of loading. Is there an easy way to load all rows at once and then arrange them into tree?

Not really… doing a tree in SQL is always slow, especially if you have to show the whole tree all at once (just showing the parents of one branch shouldn’t be bad). You either do it like you have it or select all categories and write a function to loop through them and sort them in an array. That wouldn’t be any faster unless you don’t need all the data in the tree, depends on what you’re trying to display really.

Hi Drewniacki,

have you consider storing your tree into a nested set structure ? Maybe the nestedsetbahvior can help. For instance, it provides a ‘descendants’ named scope that fetches all descendants nodes in one query.

ciao

If the tree doesn’t have many nodes, you can do this:

  • Prepare a children array in every category node

  • Read in all nodes from db

  • Loop over the nodes and create a reference to the parent’s node children array

I’ve described this here, but to make this work with ActiveRecord and without too much hassle, we first need the index feature mentioned in that topic:

http://www.yiiframew…dpost__p__60234