Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 2
Making Use Of Class NestedTree
The final thing to be done now is to use the class! Earlier in the article, I made a point of saying the class is not used for inserting or deleting of data from the tree table, in order to keep it as reusable as possible.
In the examples below I will provide SQL code for inserting data into the table. You can substitute these references for your own methods of inserting data into your database table.
Some data to work with
In the first article, the sample nested tree we looked at looked like this:
Title
------------------------
General Resources
Code Paste
Documentation
Books & Publications
Apache
PostgreSQL
MySQL
Links
Databases
Generators
PortalsTo build this into our tree, we just need to insert it into the table, then create the tree object and run the rebuild() method to setup the tree data.
Here is the SQL to get started, including the table schema and the initial data. Initially we’ll insert the nleft/nright/nlevel data all as 0, because this will be calculated shortly afterwards.
The other thing to note is that because we’re using a sequence for our ID, and manually inserting the IDs, the final step we do is tell PostgreSQL the next sequence value (this way if you insert a new row, the sequence will still behave properly).
create table nested_tree ( id serial not null, parent_id int not null, title varchar(255) not null, body text not null, nleft int not null, nright int not null, nlevel int not null, primary key (id) ); create index nested_tree_parent_id on nested_tree (parent_id); create index nested_tree_nleft on nested_tree (nleft); create index nested_tree_nright on nested_tree (nright); create index nested_tree_nlevel on nested_tree (nlevel); insert into nested_tree values ( 1, 0, 'General Resources', '', 0, 0, 0); insert into nested_tree values ( 2, 1, 'Code Paste', '', 0, 0, 0); insert into nested_tree values ( 3, 1, 'Documentation', '', 0, 0, 0); insert into nested_tree values ( 4, 1, 'Books & Publications', '', 0, 0, 0); insert into nested_tree values ( 5, 4, 'Apache', '', 0, 0, 0); insert into nested_tree values ( 6, 4, 'PostgreSQL', '', 0, 0, 0); insert into nested_tree values ( 7, 4, 'MySQL', '', 0, 0, 0); insert into nested_tree values ( 8, 1, 'Links', '', 0, 0, 0); insert into nested_tree values ( 9, 8, 'Databases', '', 0, 0, 0); insert into nested_tree values (10, 8, 'Generators', '', 0, 0, 0); insert into nested_tree values (11, 8, 'Portals', '', 0, 0, 0); select setval('nested_tree_id_seq', 11, true);
Now we check our tree data:
# select * from nested_tree; id | parent_id | title | body | nleft | nright | nlevel ----+-----------+----------------------+------+-------+--------+-------- 1 | 0 | General Resources | | 0 | 0 | 0 2 | 1 | Code Paste | | 0 | 0 | 0 3 | 1 | Documentation | | 0 | 0 | 0 4 | 1 | Books & Publications | | 0 | 0 | 0 5 | 4 | Apache | | 0 | 0 | 0 6 | 4 | PostgreSQL | | 0 | 0 | 0 7 | 4 | MySQL | | 0 | 0 | 0 8 | 1 | Links | | 0 | 0 | 0 9 | 8 | Databases | | 0 | 0 | 0 10 | 8 | Generators | | 0 | 0 | 0 11 | 8 | Portals | | 0 | 0 | 0 (11 rows)