Complete example?

[markc]

Hi, your nested set class looks concise and simple so this could be a good example to work from but it's incomplete with no html or sql schema. Do you intend to make the project more complete or is this just a WIP dump?

[ben-nsng]

Hi, the html and sql schema are almost finished and cleaning up and I think the model need some performance improved, so if you are interested feel free to work with :)

[markc]

Great. I'll just wait till you add a working example with SQL schema and I'll clone the project and see how it works. There are a few examples around but they usually require a particular framework (laravel, symphony, whatever) or complex procedures and triggers to improve performance... which is great but not when just exploring and learning how the nested set model operates. I also want something that works with SQLite (via PDO) and has a comfy Bootstrap interface so starting from a simpler example would suit me.

[ben-nsng]

Cool. Since there is a few example talking about nested set and I did not find any simple full implementation of it, and I do not want to use framework with only this function, I make it and hope developers can use it with convenient! Btw, I have pushed a sql schema and simple demo code. The next step will check if it still has bugs and focus on move node, to see if I can make it more efficiently, because it needs 13 query to move a node if the node has different level or parent, which is too costly.

[markc]

I think adding an extra "lvl" field would help reduce the sql complexity but I can't find any example code that actually takes advantage of an extra lvl column. At this point most nested set examples I've seen use procedures and triggers to overcome the overhead. One typical example...

https://github.com/riquito/Baobab/blob/master/src/sql/schema_baobab.sql

It's the same deal for the closure table examples I've seen. Looks simple enough to start with but the few mature real world examples I've seen have had to use procedures.

After reading this I am leaning back towards a materialized path model for general small to medium usage...

http://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

Or, some hybrid between it and nested sets where there are lft, rgt, lvl and perhaps pid (parent id) fields
all working together to reduce sql complexity without resorting to procedures. Or maybe instead of lft and rgt fields around the whole tree just for siblings on each level then those values would not have to be rewritten when that sub-tree is moved... sorry, idle speculation.