Closure table tree methods

[birko]

I would like to implement closure type tree in my project but i am missing some good example how to impelent it and how to retrieve the tree structure. Something like build tree in Nested type tree

[l3pp4rd]

@comfortablynumb theres no such function right? maybe you know some way to render tree?

[comfortablynumb]

No, we don't have that functionality at the moment. The problem is that we can't get the rows ordered to use something like the buildTree method of the nested set strategy. The only way to build the complete tree is to use recursion. Build the tree after any modification, and save it in cache.

If you use MySQL, using GROUP_CONCAT you can create a query to get the ordered nodes. With a query like this one, you could use something like the "buildTree" of the nested set strategy to build your tree. Take a look at this blog post from SQL Antipatterns book writer for more details:

http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

And this comment:

http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html?showComment=1331697385970#c9159108042482462253

Finally, if this functionality is critical for your app, and you have lots of nodes (or you don't use MySQL and your vendor doesn't have anything like GROUP_CONCAT), building the tree will be a really resource intensive process, since you'll need to use recursion. In this case, it would be better to pick another strategy.

[birko]

if you build a sql like

select c.id, c.title, c.url, c.parent, max(cc.depth) as level
from category c left join category_closure cc on (cc.ancestor = c.id)
group by c.id, c.title, c.url .c.parent
order by c.parent asc

you shouild have everithing to build a tree from it but I am a begginer in Doctrine2 to build such query

[comfortablynumb]

That query won't work. You're ordering by parent's id, which won't give you the correct tree structure in every case. Probably is working for you because you inserted categories like A > B > C or something like that, but as soon as you update your tree or build a more complex one, it won't work anymore. Think what happens if, in the last example, C's parent_id = 1, B's parent_id = 2 and A's parent_id = NULL. You would get this results:

A C B

Which would be the case if you first insert three root categories in this order: C, B, A, and them move them to form a tree structure like: A > B > C.

To allow correct ordering using this strategy in a portable way, we would need another field, but I think it would need lots of queries to fill this field whenever the tree is modified.

[birko]

@comfortablynumb the query should not return the rows as a tree, but should give a result that can be easly used to buildTree function like in nested set. It is a level order result.

order by c.parent asc

can be replaced by

order by max(cc.depth) asc

with the same result

[comfortablynumb]

@birko I see what you mean now. Results ordered by level should indeed work. We'll need another buildTree method implementation for this strategy, and some additional stuff to make it work.

I'll take a look at this as soon as I have some free time this week.

Thanks!

[comfortablynumb]

340