Replying on my own post for the sake of others after getting half the answer here:
‘root’ column is a sort of foreign key, but to the same table, and it is used to relate the record to its root record in the tree represented in the relational table.
My thoughts: probably for performance reasons, avoiding traversing the entire tree with multiple queries, in case it is desired and all we have in our hands is a leaf down its structure.
To the best of my knowledge what you’re doing (above) is the only way to do it.
Nested set is a way to store a single tree information in SQL tables. The fact that the behavior class supports several trees in the same table is a nice add on, but nested set in theory has nothing to do with ordering of several roots.
I guess that going with what you already did or adding your own logic for ordering of roots is the way to go and there’s no support for that in this extension, or from the the theoretical point of view.
I have this tree:
Root
- child A
- child B
- child BA
- child BB
I need/want to move child BA and BB to upper level, meaning to be children of ROOT and to delete child B afterwards.
My problem is with the first two ‘moves’. I cannot seem to move them since I use $childBA->moveAfter($ROOT) and this throws an exception telling me that “The target node should not be root”. I also took a look at insertAfter() but this method is for new records only, yet child BA and child BB already exists.
How do I accomplish this?
Thanks!
EDIT: apparently, my usage was incorrect. moveAfter() requires the first parameter, $target, to be the destined brother to the node we’re moving, not its destined parent.
Indeed I’m unable to use moveAsLast() (though I didn’t really test it) since it will fail with the mentioned error, but using moveAfter() is possible and working.
Is it possible to use the NestedSet AR in a with(), so that it loads the complete hierarchy?
Example:
AR Product BELONGS_TO Category
AR Category (with NestedSet behavior) HAS_MANY Products
Now I want to get the product with ID = 1 together with its complete category hierarchy?
The way in single queries is:
$p = Product.findByPk(1);
$cat = Category.findByPk($p->cat_id);
$hierarchy = $cat->ancestors()->findAll();
Is there a better way or at least sth. like ancestorsById() ?
Hi,
I have a similar question as the one mentioned above, basically I have 2 models, categories and products. In category model I use nested set behavior to generate the categories tree.
Currently my product model criteria looks like:
$criteria = new CDbCriteria;
$criteria->with = array(
'categories' => array(
'on' => 'categories.id=:catId',
'joinType' => 'INNER JOIN',
'params' => array(':catId' => $catId),
),
);
How could I fetch all the products from descendants categories of the current category? i tried to google but so far no solution fit my needs 
Please help!
I did something like this:
$categoryModel = ErpProductCategory::model()->findByPk($id);
$model = new ErpProduct('search');
$model->unsetAttributes(); // clear any default values
if (isset($_POST['ErpProduct']))
{
$model->attributes=$_POST['ErpProduct'];
}
// get descendants of current category
$criteria = $categoryModel->descendants()->getDbCriteria();
// get current model alias
$alias = $model->getTableAlias();
// add conditions for category
$model->getDbCriteria()->mergeWith($criteria);
// also get the products directly under the category, not only those from subcategories
$model->getDbCriteria()->addCondition($categoryModel->getTableAlias().'.id = ' . $categoryModel->id, 'OR');
// restore the correct alias
$model->getDbCriteria()->alias = $alias;
$this->render('index', array(
'dataProvider' => $model->search(),
'categoryModel' => $categoryModel,
));
The resulting query would be something along the lines (I stripped a lot of code since I’m doing a more complex query with status checks for products and parent categories):
SELECT `erpp`.*, `erppc`.*
FROM `erp_product` `erpp`
LEFT OUTER JOIN `erp_product_category` `erppc` ON (`erpp`.`category_id`=`erppc`.`id`)
WHERE (((`erppc`.`lft`>6 AND `erppc`.`rgt`<9) AND (`erppc`.`root`=:ycp1)) OR (erppc.id = 3)) ORDER BY `erppc`.`lft` LIMIT 10. Bound with :ycp1='1'
Thanks a lot I got it working 
Hello. There is an error in "Useful code (Non-recursive tree traversal)" block:
http://www.yiiframework.com/extension/nestedsetbehavior/
in line: for($i=$level-$model->level;$i;$i--)
correct code: for($i=$level-$category->level;$i;$i--)
Could you fix it, please.
Thank you.
Hi am looking around some help.
What If I have products and category related (fk), and I need to delete categories?
what happen with the related category_id on products table?
Sorry my English or if its bad formulated the question.
How do I move one node and all of its children to a new root?