MPTtree, Hieararchical trees in a database table

[eluser]sshz[/eluser]
[quote author="TheFuzzy0ne" date="1241274842"]I don't see why not. Are you sure you've indexed the tables correctly?[/quote]

I'm new to this. What is the right way to index tables for MPTTree?

[eluser]TheFuzzy0ne[/eluser]
The id field should be the primary key, and the lft and rgt fields should also have an index.

Please post your table schema in this forum. For example, mine is:

Code:

CREATE TABLE `forums` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) collate utf8_unicode_ci NOT NULL,
  `description` varchar(255) collate utf8_unicode_ci NOT NULL,
  `lft` int(10) unsigned NOT NULL,
  `rgt` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lft` (`lft`),
  KEY `rgt` (`rgt`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[eluser]sshz[/eluser]
TheFuzzy0ne, thanks.

Now my schema is:

Code:

CREATE TABLE `pyro`.`goods` (
`id` int(10) unsigned NOT NULL auto_increment,
`lft` int(10) unsigned NOT NULL,
`rgt` int(10) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
`long_title` VARCHAR( 255 ) NOT NULL ,
`short_desc` VARCHAR( 255 ) NOT NULL ,
`long_desc` VARCHAR( 255 ) NOT NULL ,
`price` VARCHAR( 10 ) NOT NULL ,
`pic` VARCHAR( 100 ) NOT NULL ,
`sort` INT( 10 ) NOT NULL ,
`special` INT( 10 ) NOT NULL,
PRIMARY KEY  (`id`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`)
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci

It's got a lot better with indexing, get_children query for 6,500 elements tree took only 5 seconds, but it's not good enough, any more clues?

Code:

$root = $this->MPTtree->get_root();
        $children = $this->MPTtree->get_children($root['lft'], $root['rgt']);

[eluser]TheFuzzy0ne[/eluser]
Hmmm. Have you tried optimizing/repairing the table? 6,500 nodes is a lot! I can't help thinking that you can make it work better. Only categories should be stored in the tree, not the items themselves.

[eluser]jacobc[/eluser]
I'm curious...
How come this is implemented as a model... and not a library.

If I was writing this, I would have extended the Model library as you have done, but keep it as a library... Then have models that extend the MPTT.

Also as someone mentioned earlier... I don't understand why you don't use a parent_id column as well. makes checking immediate parent or children so much simpler... Especially get_children().

As in http://www.sitepoint.com/article/hierarc...atabase/2/ which you seem to have for your example image... the article uses a parent field.

[eluser]m4rw3r[/eluser]
The reason for not using the parent_id field is that this is an Modified Preorder Tree Traversal model, not an Adjacency Tree model. The advantages of the MPTT is that it is a lot faster in many queries and also makes sure that a node doesn't get orphaned.
The benefit with Adjacency trees are that they are faster when just getting one level of nodes.

Also, I implemented it as a model because I was young and dumb...
When I rewrite it I will make it a library.

[eluser]antonumia[/eluser]
[quote author="pistolPete" date="1224073022"]
I would recommend also using the latest Ext JS (which is v 2.2 at the moment).

If you want to, I can post an updated example...[/quote]


hi pistolPete,

I had a similar issue today and tried replacing extJS files with v2.2.1. this broke it completely.

I can move and delete no problem. It's just adding that isn't working:

Error: syntax error
Source File: http://localhost/insight/assets/static/t...ext-all.js
Line: 25, Column: 12
Source Code:
[node] => 26

firebug is complaining about the > in [node] => 26 if that helps.

do you have an updated example?


anton

[eluser]TheFuzzy0ne[/eluser]
[quote author="m4rw3r" date="1241721094"]Also, I implemented it as a model because I was young and dumb...
When I rewrite it I will make it a library.[/quote]

This is a bit off-topic, but why a library? I thought making it a model was a good call.

[eluser]m4rw3r[/eluser]
Well, the difference between a model and a lib in this case would be that you included the lib and then extended it to have specialized models.
You "can't" do that with a model (it doesn't seem right, according to the CI structure).

[eluser]crises[/eluser]
Thanks to the author, this save me a lot of time coding. I really appreciate it. But i think i have found a little mistake in the code (dunno if was posted before).

On line 745:

Code:

$this->db->select('title');

Maybe should be?:

Code:

$this->db->select($this->title_col);

Because if my title column is not named 'title' won't work.