Zend recursive table dependencies data retrival

Question

I have three tables, incidents, incident_properties and property_types.

What I would like to do is:

1. Query the incidents table for one row
2. Retreive all its properties (which are key-value rows and a type_id)
3. For each property, retrieve its type from the property_type table

So I set up this table relationship -

class Incidents extends Zend_Db_Table_Abstract
{
    protected $_name = 'incidents';
    protected $_dependentTables = 'Properties';

}

class IncidentProperties extends Zend_Db_Table_Abstract
{
    protected $_name = 'incident_properties';
    protected $_dependentTables = 'PropertyTypes';
    protected $_referenceMap = array(
        'Incidents' => array(
            'refTableClass' => 'Incidents',
            'refColumns' => 'incident_id'
        )
    );
}

class PropertyTypes extends Zend_Db_Table_Abstract
{
    protected $_name = 'incident_property_types';
    protected $_referenceMap = array(
        'Properties' => array(
            'refTableClass' => 'IncidentProperties',
            'refColumns' => 'property_type_id'
        ) 
    );
}

In my incidents model mapper I would like to do something like:

$select = $this->_dbTable->select()->where('id = ?',$incident->get_id());

$incident_properties = $this->_dbTable
                            ->fetchRow($select)
                            ->findDependentRows('IncidentsProperties')
                            ->toArray();
print_r($incident_properties);

And retrieve in $incident_properties the property key, value and type within its type row.

Any ideas how to accomplish this the right way?

Answer 1

Well, lets go.

1: You always have to add the full classname in $_dependentTables. So you do not add the tablename from your database, but the classname of your instance of Zend_Db_Table_Abstract.

So it should be:

class Incidents extends Zend_Db_Table_Abstract
{
    protected $_name = 'incidents';
    protected $_dependentTables = 'IncidentProperties';

}

2: You should add a columns attribute to your referenceMap like this:

protected $_referenceMap = array(
    'Incidents' => array(
        'refTableClass' => 'Incidents',
        'refColumns' => 'incident_id',
        'columns' => 'name of the column that references the incident_id'
     )
);

Ok so what you want to do is this:

class IncidentMapper
{
    protected $_dbAdapter; //an instance of your Incident-Class extending Zend_Db_Table_Abstract

    public function doSomeStuff($incidentID)
    {
         $incident = $this->_dbAdapter->find($incidentID);
         //fetch dependent rowsets using incident_id
         $incidentProperties = $result->findDependentRowset('IncidentProperties', 'Incidents');
         foreach($incidentProperties as $incidentProperty)
         {
             //fetch parent row using property_type_id
             $propertyType = $incidentProperty->findParentRow('PropertyTypes', 'Properties');
         }
    }
}

And then if you want to use the adapters as data-arrays you have to call

->toArray()

on your results.

So at first, you receive an instance of your Incidences-Class via

->find($incidentID)

Then you call

->findDependentRowset('ReferencedClass', 'Rule')

to get all IncidentProperties with the ID of the fetched incident. Afterwards you loop through all found properties and call

->findParentRow('ReferencedClass', 'Rule')

to get the PropertyType.

I hope it helps. If you need more information have a look at the ReferenceGuide - Zend_Db_Table

At first it is a little bit hard to understand and confusing. If you have any other questions, feel free to ask.

EDIT:

At the moment i am not quite sure if you receive the properties as adapters or as data arrays. (And unfortunately i am not able to test it right now) So if you run into problems you probably have to fetch every single IncidentProperty as following:

$_incidentPropertyAdapter->find($incident_property_id)

before you can call

->findParentRow(...)