Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 2

Starting The Class

In the first part of this series, we had a brief look at the SQL that would be used to represent the tree.

This is very similar to the phpRiot article database table, but to make it closer we’ll add an extra field to the schema which stores the article body.

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);

In this table, our tree will be sorted alphabetically by the title field. To relate this back to the PhpRiot articles table, the body column stores the actual data for an article. We don’t reference this column in our tree class, but it shows how easily the tables can have any other arbitrary data that doesn’t relate to the tree.

Framework and constructor

Earlier we listed a few of the key requirements of the class, which mainly related to being adaptable to multiple database tables, each of which will have its own unique set of data.

To deal with this, we pass a database table name and a list of fields to the constructor. For the purposes of this article we assume a PostgreSQL database connection has already been made. In fact, you probably rather use your own database abstraction tool, but since most people differ in this area, I’ve used the native PHP PostgreSQL functions.

We will assume the code is placed in a file called NestedTree.class.php, available from your include_path.

<?php
    class NestedTree
    {
        /**
         * Constructor. Set the database table name and necessary field names
         *
         * @param   string  $table          Name of the tree database table
         * @param   string  $idField        Name of the primary key ID field
         * @param   string  $parentField    Name of the parent ID field
         * @param   string  $sortField      Name of the field to sort data.
         */
        function NestedTree($table, $idField, $parentField, $sortField)
        {
            $this->table = $table;
 
            $this->fields = array('id'     => $idField,
                                  'parent' => $parentField,
                                  'sort'   => $sortField);
        }
 
        /**
         * A utility function to return an array of the fields
         * that need to be selected in SQL select queries
         *
         * @return  array   An indexed array of fields to select
         */
        function _getFields()
        {
            return array($this->fields['id'], $this->fields['parent'], $this->fields['sort'],
                         'nleft', 'nright', 'nlevel');
        }
    }

There are three field names passed to the constructor. The first is the name of the primary key identifier field of the table. The second is the name of the column that stores the identifier of the row’s parent record, while the third column is the one we sort the tree by. This is only used when rebuilding the tree data.

The second function (getFields()), is a utility function to return an array of the all fields we need to select when performing an SQL select query. Of course, we could just use select *, but if your table has a lot of extra data, this is an unnecessary load on your server and potentially a large performance hit.

So if we were to create a tree for the above nested_tree SQL table, the PHP would look like:

<?php
    require_once('NestedTree.class.php');
    $tree = new NestedTree('nested_tree', 'id', 'parent_id', 'title');
?>

One thing to be aware of here is that PostgreSQL’s varchar sorts are case-sensitive, so you may want to change title to lower(title).

• Next: Data Extraction Functions