Zend_Db_Table and tables relationships
When developping a database tied application, you eventually come to a point where you get (at least) two tables with a parent/child relationship, such as for example a User table referenced by, say, a Post table (each post belonging to a specific user). That's basically the point where you need Zend_Db_Table relationships mechanism. the drawback of this mechanism is that, as far as I know, it does not produce joined queries to retrieve the parent data, but fires a query for each parent row. Thus instead of just using Zend_Db Relationships, I developped a simple yet effective auto-join mechanism that I called, in great simplicity, parent mapping. It supports multiple joins from multiple tables, remote fields specification and prefixing.
It is included in my db table class and you can see the interesting part of the code below for your convenience (Ignore the 3 first lines of the function as it is used for something else in my version of the framework).
[php]
/**
* Holds the parent mapping for join in fetchAll
*
* <code>
* array(
* 'remote_table' => array(
* 'remote' => 'id'
* 'local' => 'remote_id',
* 'fields' => array('foo', 'bar', 'prefix' => 'remote_'),
* ),
* );
* </code>
*
* @var array
*/
protected $_parentMap = array();
/**
* Honors the parent mapping from self::_parentMap
*
* @param string|array $where
* @param string|array $order
* @param integer $count
* @param integer $offset
* @return Zend_Db_Table_Rowset
*/
public function fetchAll($where = null, $order = null, $count = null, $offset = null) {
if (!is_array($this->_cols)) {
return parent::fetchAll($where, $order, $count, $offset);
} else {
$db = $this->getAdapter();
$select = $db->select();
$select ->from($this->_name, $this->_cols, $this->_schema)
->order($order)
->limit($offset, $count);
if (!is_null($where)) {
$select->where($where);
}
if (!empty($this->_parentMap)) {
foreach($this->_parentMap as $parentTable => $specs) {
$fields = array();
if (isset($specs['fields']['prefix'])) {
$prefix = $specs['fields']['prefix'];
unset($specs['fields']['prefix']);
foreach($specs['fields'] as $key => $field) {
if (is_int($key)) {
$key = $prefix.$field;
}
$fields[$key] = $field;
}
} else {
$fields = $specs['fields'];
}
$select->join(
$parentTable,
sprintf('%s.%s = %s.%s',
$db->quoteIdentifier($this->_name),
$db->quoteIdentifier($specs['local']),
$db->quoteIdentifier($parentTable),
$db->quoteIdentifier($specs['remote'])
),
$fields
);
}
}
$stmt = $db->query($select);
return $this->_makeRowset($stmt);
}
}
Comments
how about using propel for such needs ?
while propel is certainly a good way to handle ORM, it is not (yet ?) the way to do things in ZF (for some reasons, like "no third-party dependency" or somtehing like that I guess). Thus I'm sticking with the ZF way, which is not necessarily very clear on why we do things, as you point out on your blog with Zend_Json.
Plus I don't like propel (for having used it in symofny) :)
Ah! The Zend_Json... ;-)
As more and more people are using it, I'm sure the Zend Framework will come up with a specific and complete ORM solution. I just wish it won't rely on xml description files like propel, which I agree is a pain.
Thanks for the code, it has come in useful. Here is modified version with a addition of fetchRow that handles the relationships. (the actual mapping was moved from fetchAll to a class member)
http://code.ryvondesigns.com/riskle...
http://code.ryvondesigns.com/riskle...
thanks for the patch, I'll apply it soon :)
You SVN repository is down it's imposible to download the source ...
Could you put a ZIP archive on your website
Hi Sebas, my repository has moved to assembla (http://www.assembla.com/), I've updated the link to reflect this change. Thanks for your report.