How to use Doctrine nested set for storing multiple trees in one single table?

Question

I am planning on using the nested set feature the orm doctrine provides to have threaded comments. In other words, i want to have multiple independent trees stored in one table. In my specific case there would be some entity "news" having lots of "comment"s.

News:
  columns:
    content: clob

Comment:
  actAs:
    NestedSet:
      hasManyRoots: true
      rootColumnName: root_id
 columns:
    benutzer_id:       { type: integer, notnull: true }
    ref_id:            { type: integer, notnull: true }
    content:            { type: clob, notnull: true}
  relations:
    News:
      class: News
      local: ref_id
      foreign: id

How do i tell doctrine to use comment.ref_id as discrimminator-column? So as a result only nodes having the same ref_id relate to one tree. Currently all tree operations effect all nodes stored in the comment-table. Desired is, that only nodes with a given column name ("ref_id") will act as one single tree.

update For solving the issue I was thinking of producing a way to have many hasManyRoots-trees in one table. To load a tree one would have to create a tree like this:

$category->$treeObject = Doctrine_Core::getTable('Category')->getTree('ref_id',12);

All tree manipulation actions should then include "WHERE ref_id=12 AND ...". In my case you'd manipulate the comments-tree for news #12. Hereby the database-update-statements would be less. Since ref_id relates to the news, there's already an index on ref_id and so it should run quite fast.

final solution - NOT PART OF THE QUESTION

through a lot of discussion and sleeping over it i came up with the following schema. it includes reduction of columns in the comment-table (kicked out the ref_id, root_id now references the root-comment not the news anymore).

News:
  columns:
    content: clob
    comment_root_id:      { type: integer, notnull: false }
  relations:
    CommentRoot:
      class: Comment
      local: comment_root_id
      type: one

Comment:
  actAs:
    NestedSet:
      hasManyRoots: true
  columns:
    content:            { type: clob, notnull: true}

I think this is more clean this way. Creating a News requires to create a dummy-root-node.

        $treeObject = Doctrine_Core::getTable('Comment')->getTree();

        $root = new Comment();
        $root ->content = 'root';
        $root->save();
        $root = $treeObject->createRoot($root);

        $news->setCommentRoot($root);
        $news->save();

And finally you can use a left join when querying "News" to fetch the root-comment to tell you how many children there are.

For performance issues you may wanna put an index manually on the root_id-column. Done.

Answer 1

the value for rootColumnName is the descriminator so if you wan tto use ref id your would do:

  actAs:
    NestedSet:
      hasManyRoots: true
      rootColumnName: ref_id

UPDATE:

For solving the issue I was thinking of producing a way to have many hasManyRoots-trees in one table.

Youre trying to rework additional functionality! :-)

Calling getTree doesnt actually runa query... it just returns the implementation instance - Doctrine_Tree_NestedSet. YOu have a chance to what you want before you query...

To just get a certain tree:

$category->$treeObject = Doctrine_Core::getTable('Category')->getTree()->fetchTree(array('root_id' => 12));

The first argument to fetchTree is an array of options you can specify mutltiple things here including

• the root_id (will be translated to whatever you supplied as the root_id option in your schema)
• the depth to fetch as depth

Additionally, if you need more complex options than that you can supply a base query before you fetch the tree that has whatever query criteria you want on it:

// join you news item
$q = Doctrine_Core::getTable('Category')->create('c')
  ->leftJoin('c.News n with n.id = ?', $articleId);

$tree = Doctrine_Core::getTable('Category')->getTree();
$tree->setBaseQuery($q);
$nodes = $tree->fetchTree(array('root_id' => 12));

The root_id of the tree should always point to another node in the tree... not something external. By using setting the base query you can run your join on the entire tree or supply other qualifying conditions.

---

However not that ref_id should not have a FK constraint applied. Roots and trees are managed with the Tree and Node API's which takes care of all this for you. The SQL it will produce for the NestedSet related columns something like this:

CREATE TABLE category (id BIGINT AUTO_INCREMENT,  
ref_id INT, 
lft INT, 
rgt INT, 
level SMALLINT, 
PRIMARY KEY(id)) ENGINE = INNODB

If you ant to save yourself headaches leave these column definitions (Aside from naming if thats not agreeable) you will save yourself headaches. If you need to relate back to news use a separate column/relation thats not associated with the actual nested set.