Implementing An N-Level Nested Tree In PHP And PostgreSQL Appendix

Get Immediate Family

This is a function used to fetch all the immediate family around a particular node.

It works just like it would if the node were you – it would fetch your parent (okay so the nested tree model only allows for one parent), your siblings (brothers and sisters), and your children.

Why is this useful

Being able to do this is really useful when dynamically displaying a site navigation. It would be cumbersome to list the entire site navigation, so this just shows items around the current node.

Let’s say you have a tree like:

Title
------------------------
General Resources
   Code Paste
   Documentation
   Books & Publications
      Apache
      PostgreSQL
      MySQL
   Links
      Databases
      Generators
      Portals

This kind of resembles parts of the PhpRiot web site.

If we used Get Immediate Family around, say, the “Links” node, we would be returned with something that looked like this:

Title
------------------------
General Resources
   Code Paste
   Documentation
   Books & Publications
   Links
      Databases
      Generators
      Portals

As you can see, it hid the data from the Books & Publications section. This is a good thing, as the user wasn’t in this section anyway, however they can still get to this section easily.

How to fetch this data

We really only need two pieces of information to fetch this data:

• The node’s ID
• The node’s parent ID

There are really three operations for this query:

• Find parent: find the node with ID equal to the initial node’s parent ID
• Find siblings: find the node(s) with parent ID equal to the initial node’s parent ID (if the node is an only child then only it will be returned here)
• Find children: find the node(s) with parent ID equal to the initial node’s ID

The new PHP function

This new function slots directly into the class created in Page 7 of Implementing an N-Level Nested Tree in PHP and PostgreSQL, Part 2

<?php
    class NestedTree
    {
        // ... other code ...
 
        /**
         * Fetch the immediately family of a node. More specifically, fetch a node's
         * parent, siblings and children. If the node isn't valid, fetch the first
         * level of nodes from the tree.
         *
         * @param   int     $id             The ID of the node to fetch child data for.
         * @return  array                   An array of each node in the family
         */
        function getImmediateFamily($id)
        {
            $node = $this->getNode($id);
            $idField = $this->fields['id'];
            $parentField = $this->fields['parent'];
 
            if ($node->$idField > 0) {
                // the passed node was valid, get the family
                $query = sprintf('select %s from %s where %s = %s or %s = %s or %s = %s order by nleft',
                                 join(',', $this->_getFields()),
                                 $this->table,
                                 $idField, $node->$parentField,
                                 $parentField, $node->$parentField,
                                 $parentField, $node->$idField);
 
            }
            else {
                // the passed node did not exist, get the first level of nodes
                $query = sprintf('select %s from %s where %s = 0 order by nleft',
                                 join(',', $this->_getFields()),
                                 $this->table,
                                 $parentField);
            }
 
            $result = pg_query($query);
 
            $arr = array();
            while ($row = pg_fetch_object($result)) {
                $row->num_descendants = ($row->nright - $row->nleft - 1) / 2;
                $arr[$row->$idField] = $row;
            }
            return $arr;
        }
 
        // ... other code ...
    }
?>

As you can see, the function simply gets the top level of nodes, if the passed node does not exist.

Additionally, at the we also calculate the number of descendants each node has. There’s no specific reason for this here, but the data can be useful.

Other Options

• Download a PDF version of this article
• Put your PHP knowledge to the test with our online and iPad/iPhone quizzes
• View or post comments for this article
• Browse similar articles by tag: MySQL, PHP, PostgreSQL
• Read related articles:
  • Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 1
  • Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 2

Article History

May 11, 2005

Initial article version

May 26, 3586

Updated getImmediateFamily() method to correctly reference the $fields property