DB schema:

Category:
actAs:
NestedSet:
hasManyRoots: true
rootColumnName: root_id
columns:
id:
type: integer(4)
primary: true
autoincrement: true
name:
type: string(64)
notnull: true
image: string(64)
indexes:
tree:
fields: [lft, rgt, root_id]

Sample data:

id: '1'
name: 'Przykładowa kategoria 1'
image: null
root_id: '1'
lft: '1'
rgt: '6'
level: '0'

• id: '2'
  name: 'Przykładowa kategoria 2'
  image: null
  root_id: '2'
  lft: '1'
  rgt: '6'
  level: '0'
  -
  id: '3'
  name: 'Przykładowa podkategoria 1'
  image: null
  root_id: '2'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '4'
  name: 'Przykładowa podkategoria 2'
  image: null
  root_id: '2'
  lft: '3'
  rgt: '4'
  level: '2'
  -
  id: '5'
  name: teset1
  image: null
  root_id: '1'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '6'
  name: test2
  image: null
  root_id: '1'
  lft: '3'
  rgt: '4'
  level: '2'

When using HYDRATE_RECORD_HIERARCHY, the first top-level category is empty. Everything is assigned to the other one. Only single-root trees work properly.

When combining SoftDelete and NestedSet behavior, there's no way of calling hardDelete method on node object. According to documentation, to peform a delete on a nested set, delete should be called in node object, which will call delete method on the object itself.

The best way I can explain the issue is with code. Please see the below:

<?php

$root = new Test();
$root->name = '1';
$root->save();

// Create root node
$tree = Doctrine::getTable('Test')->getTree();
$tree->createRoot($root);

// Create child node
$child1 = new Test();
$child1->name = '2';
$child1->save();

// Add child
$child1->getNode()->moveAsLastChildOf($root);

// Create child node
$child2 = new Test();
$child2->name = '3';
$child2->save();

// Add child2 as node of child1
$child2->getNode()->moveAsLastChildOf($child1);

// Create child node
$child3 = new Test();
$child3->name = '4';
$child3->save();

// Add child3 as node of child2
$child3->getNode()->moveAsLastChildOf($child2);

// Add another root just to be nice
$root2 = new Test();
$root2->name = '5';
$root2->save();

// Create root node
$tree->createRoot($root2);

/**
 * Now we have the following tree (Each '-' indicates 1 level):
 * 1
 * - 2
 * - - 3
 * - - - 4
 * 5
 */

/**
 * Lets say I want to move node '3' to be a root.
 * With this I assume that all of the current nodes
 * children will be moved with it:
 */
$tree->createRoot(child2);

/**
 * Now the (implied) tree should look like this:
 * 1
 * - 2
 * 3
 * - 4
 * 5
 * 
 * Instead, the tree actually looks like this:
 * 1
 * - 2
 * - - - 4
 * 3
 * 5
 */

/**
 * I will now demostrate incorrect moving back of child nodes.
 */
$child2->getNode()->moveAsLastChildOf($child1);
/**
 * Now the tree should go back to looking like this:
 * 1
 * - 2
 * - - 3
 * - - - 4
 * 5
 * 
 * But the tree now looks like this:
 * 1
 * - 2
 * - - - 4
 * - - 3
 * 5
 */

Tree hierarchy hydrators (Doctrine_Collection::toHierarchy and Doctrine_Array_Hierarchy_Driver::hydrateResultSet) are checking wheter the column `level` exists.

The level column can be aliased, and for oracle, it is required to do so. Therefor it is better to check, whether the aliased field level exists.

Patch included in pull request

Sorry for the lengthy explanation, couldn't make it more straight forward:

I have a model which is similiar to a nestet set but the tree structure needs to overlap:

For Model A, every Object A1 can have multiple descendant objects A2 and in turn can be a descendant of multiple objects A0.

Since I saw no way to do this with Nested-Set Relations (or Equal-Nested-Sets) I have set up my Model like this:

modules:
columns: ..<do not matter>
relations:
Children:
class: modules
refClass: modules_required
Parents:
class: modules
refClass: modules_required

modules_required:
columns: <do not matter here, just 2 foreign key columns>
relations:
Children:
Parents:

I needed to specify the Relations on both tables, to use onDelete/onUpdate CASCADE rules. Generated Models look fine, just as intended.
(Every Class has many Children and has many Parents...)

Now the strange part:
When I update an object of modules (say id=18), Doctrine issues the following queries:
DELETE FROM modules_required WHERE (required_id = ? AND module_id IN (?, ?, ?, ?, ?)) - (18, 25, 26, 32, 34, 35)
// where 25 to 35 are CHILDREN of 18
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (25, 25, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (26, 26, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (32, 32, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (34, 34, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (35, 35, 10)
UPDATE modules_required SET required_id = ? WHERE module_id = ? AND required_id = ? - (25, 25, 12)
//where 10 and 12 are PARENTS of 18
and somewhen, Doctrine encounters an MySQL ERROR because of the previous update marathon:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '25-25' for key 'PRIMARY'

The point is:
1. why is Doctrine trying to create self-referencing relations, and
2. why is it touching the relation at all, when i only did change some text fields in the object?

Is there a better way to solve my problem?

How to reproduce

1. fetch a tree with nested set

   $root = Doctrine::getTable($model)->getTree()->findRoot($rootId);
   $treeObject= Doctrine::getTable($model)->getTree()->fetchTree($root->getId()); 
   

2. issue

   $root->getNode()->getChildren();
   
   or there will be a total query mess in a lambda function with a loop getChildren().
   ...
   <?php foreach($root->getChildren() as $child): ?>
      ....
   <?php endforeach; ?>
   ...
   
   

Problem is described more verbosely here – http://codeutopia.net/blog/2008/08/30/understanding-doctrines-nestedset-feature/

What behavior is expected

An already fetched record instance is returned, without calling database and without causing query flooding.

Like in propel.

In propel if you are calling ....::retrieveTree($id) it returns the root
with completed, pooled (cached) and linked tree.

What happens

An additional query is issued

This is a big slip in architecture of Doctrine`s nested set implementation.

When trying to create new root node, the following exception is thrown.

PHP Fatal error: Uncaught exception 'Doctrine_Tree_Exception' with message 'Node must have a root id set or must be persistent and have a single-valued numeric primary key in order to be created as a root node. Automatic assignment of a root id on transient/new records is no longer supported.'

This only happens when hasManyRoots is true, and table have multiple columns in primary key.

Example:

$category = new Menu();
$category->name = 'Menu Root';
$category->language_id = 1;
$category->root_id = 23;
$category->save();

$treeObject = Doctrine::getTable('Menu')->getTree();
$treeObject->createRoot($category);

and menu table is defined as follows:

$this->hasColumn('id', 'integer', 8, array(
'type' => 'integer',
'primary' => true,
'autoincrement' => true,
'length' => '8',
));
$this->hasColumn('language_id', 'integer', 8, array(
'type' => 'integer',
'primary' => true,
'length' => '8',
));

If i remove primary option from language_id column, everything works as it should.

The equal nest relation works incorrectly twice. The SQL it produces is wrong. And even if correct the SQL it still returns wrong data.
In my example below $profile->getFriends() returns incorrect data and even tries to write these wrong data when I do $profile->setEmail("..."); $profile->save()
I'm certain that SQL query in Nest.php is wrong, but not only SQL. Even after fixing SQL the data is still incorrect. I digged through the code and suspect that the problem is in Doctrine_Collection which seems to works with only one referenceField, while in an "equal relationship" this field varies row to row.

Here goes steps to reproduce.

Schema.yml
==========

Profile:
columns:
id:
type: integer
primary: true
autoincrement: true
email:
type: varchar(255)
notnull: true
relations:
Friends:

Fixtures:
======

Profile:
-
id: 1
email: one@example.org
-
id: 2
email: two@example.org
-
id: 3
email: three@example.org
-
id: 4
email: four@example.org

UserFriend:
-
requestor_user_id: 1
receiver_user_id: 2
-
requestor_user_id: 4
receiver_user_id: 1
-
requestor_user_id: 2
receiver_user_id: 3

PHP test
=======
$user = Doctrine_Core::getTable('Profile')->find(1);
var_dump($user->getFriends()->toArray());

Output
======
array(2) {
[0]=>
array(3) {
["id"]=>
string(1) "2"
["email"]=>
string(15) "two@example.org"
["UserFriend"]=>
array(2) {
[0]=>
array(3)

[1]=>
array(3)

}
}
[1]=>
array(3) {
["id"]=>
string(1) "4"
["email"]=>
string(16) "four@example.org"
["UserFriend"]=>
array(1) {
[0]=>
array(3)

}
}
}

As it can be seen from output, the relation "UserFriend" shows insane data:

• sometime there are two records in "UserFriend" - and that's wrong
• user 4 has friendship with 4 - that's also wrong, not in the fixtures.

Hi,

When inserting node as direct children of a root node, it seems the root node is not populated with new lft/rgt values...

Here is a test case, simply made of the code samples given in documentation :
http://www.doctrine-project.org/projects/orm/1.2/docs/manual/hierarchical-data/en#nested-set:working-with-trees:creating-a-root-node

/*
 * Creating a Root Node
 */
$category = new Category();
$category->name = 'Root Category 1';
$category->save();

$treeObject = Doctrine_Core::getTable('Category')->getTree();
$treeObject->createRoot($category);


/*
 * Inserting a Node
 */
$child1 = new Category();
$child1->name = 'Child Category 1';

$child2 = new Category();
$child2->name = 'Child Category 1';

$child1->getNode()->insertAsLastChildOf($category);
$child2->getNode()->insertAsLastChildOf($category);




/*
 * BUGGY !
 */
$category->getNode()->hasChildren();   // will return false, we except true as we just instert 2 children to this root node...


$category->refresh();
$category->getNode()->hasChildren(); // true... that's now ok !

If I detach a node from the tree using $node->detach(); it set the left and right value to zero without shifting, creating a possible tree corruption.
It still work somehow because inserting a new node will shift the value but keep the hole in the tree.

I've looked to the original commit in Trac (http://trac.doctrine-project.org/changeset/4089) and I don't think the original idea of the commit was to leave a hole.
I've added 3 lines of code (that I copied from the delete function which works properly), the coresponding patch is joigned to the bug ticket