PHP recursive function to retrieve hierarchical data from flat table (Zend Framework)

Question

I'm trying to retrieve hierarchical data from a table but am failing to do so. The table has (for now) the following columns: ifc_key, ifc_name, ifc_parent. ifc_key is not used. (primary key, but not used for this function.

The purpose is to get an array. Each element is a "parent" interface. (so all these root elements are ifc_name values that don't have an ifc_parent set (equals ifc_name if set).

Consider the following layout (demo):

ifc_key | ifc_name | ifc_parent
0 | parent_ifc |
1 | a0a | parent_ifc
2 | a0b | parent_ifc
3 | b0a | vif1
4 | b0b | vif1
5 | vif1 | a0a

So the array I'm looking for, generated from a query is:

Array
(
[parent_ifc] => Array
    (
        [a0a] => Array
            (
                [vif1] => Array
                    (
                        [0] => b0a
                        [1] => b0b
                    )

            )

        [a0b] => 
    )
)  

The function I came up with is underneath this paragraph. I wanted to created a recursive function, which calls itself upon finding children but the problem is that none of the children are selected upon the first call to this method. (those with empty parent are parents themselves). So I only get the parents back, but none of the children (and possibly their children, etc - this can in theory be indefinite).

public static function getByFilerOrganisedChildren($filer_id, $parent = '')
{
    $table   = new Filer_Interface_Table();
    $where[] = $table->getAdapter()->quoteInto('ifc_system_id = ?', $filer_id);
    $where[] = $table->getAdapter()->quoteInto('ifc_parent = ?', $parent);
    $rows    = $table->fetchAll($where, 'ifc_parent ASC');

    foreach ($rows as $row) {
        if ($row->ifc_parent == '') $data[] = $row->ifc_name;
        else {
            $data[$row->ifc_parent][] = $row->ifc_name;
            self::getByFilerOrganisedChildren($filer_id, $row->ifc_parent);
        }
    }

    return (isset($data) ? $data : false);
}

Answer 1

Can you write how you call this function? with what are the parameters? And I think you must make $data private static fields of your class

Answer 2

You made no reference to the ifc_system_id column before your method so I'll assume that's not immediately pertinent to the question. Also the output you specify as desirable is actually inconsistent.

The key that you're missing, it seems, is to call the recursive function using data relevant to the child records - in this case, ifc_name rather than ifc_parent.

public function getByFilerOrganisedChildren($filer_id, $parent = '')
{
    $table   = new Filer_Interface_Table();
    $where[] = $table->getAdapter()->quoteInto('ifc_system_id = ?', $filer_id);
    $where[] = $table->getAdapter()->quoteInto('ifc_parent = ?', $parent);
    $rows    = $table->fetchAll($where, 'ifc_parent ASC');

    $data = array();
    foreach ($rows as $row) {
        $data[$row->ifc_name] = $this->getByFilerOrganisedChildren($row->ifc_name);
    }

    return (! empty($data) ? $data : false);
}

Answer 3

I'm not answering your question here, but: this is not very effective way to store hierarchical data in the database.

I'd rather recommend Nested Set (e.g. there are easy implementations in Doctrine ORM and Zend Framework [proposal]).

You may also try alternative method, described in this article (which is in Polish, but the provided SQL and PHP samples are universal).

Answer 4

public static function getByFilerOrganisedChildren($filer_id, $parent = '')
{
    $table   = new Filer_Interface_Table();
    $where[] = $table->getAdapter()->quoteInto('ifc_system_id = ?', $filer_id);
    $where[] = $table->getAdapter()->quoteInto('ifc_parent = ?', $parent);
    $rows    = $table->fetchAll($where, 'ifc_parent ASC');

    foreach ($rows as $row) {
        $data[$row->ifc_name] = array();
        /* self::getByFilerOrganisedChildren($filer_id, $row->ifc_name); */
    }

    Zend_Debug::dump($data);

    return (isset($this->data) ? $this->data : false);
}  

With the commented line above, the output by Zend_Debug::dump() is:

array(7) {
["cifs-80"] => array(0) {
}
["e0M"] => array(0) {
}
["lo"] => array(0) {
}
["vif1"] => array(0) {
}
["vif1-81"] => array(0) {
}
["vif1-82"] => array(0) {
}
["vif1-83"] => array(0) {
}
}

These are all the "parents". But as expected (since the call to the table is "ifc_parent = ''") none of the children (and their possible children) is returned.

However when I uncomment the line:

self::getByFilerOrganisedChildren($filer_id, $row->ifc_name);  

The output is:

NULL  

I guess it's because of:

abstract class Filer_Interface_Abstract extends Filer_Db_Class  

But I'm unable to change this - since it's how the current application is built. Suggestions?

Answer 5

My code

DB scheme:

create table roles (
    id      int not null auto_increment,
    name    varchar(50) UNIQUE not null,
    inherit_id  int
)

PHP code:

class Default_Model_Roles extends Zend_Db_Table_Abstract {

   protected $_parents = array();

    public function getParents($id) {
        $this->_getAllParents($id);
        return (! empty($this->parents) ? $this->parents : FALSE);
    }

    public  function _getAllParents($id) {
        $select = $this->select();
        $select->where('id = ?', $id)
           ->order('id');
        $row = $this->fetchRow($select);
        $this->parents[] = $row->id;
        if ($row->inherit_id != NULL)   
            $this->_getAllParents($row->inherit_id);
    }
}

Use:

$table = new Default_Model_Roles();
$parents = $table->getParents($this->role_id);