Zend Framework - Relationships and ORM Optimization
I've been using ZF for a few months now and I'm really happy with it, but I'm not entirely sure how to work with model relationships and avoid multiple db queries at the same time. Many people have this problem and no one seems to find a good solution for it. (and avoid using a third party ORM) For example, I have a list of users and each user belongs to a group. I want the user list to display user information and group name (for tables: users and groups. Users have a foreign key for groups of tables). I have: 2 mapping classes to handle these tables, UserMapper and GroupMapper. 2 Custom and group class classes 2 data source classes that extend Zend_DB_Table_Abstract
in userperper I can do findParentRow to get the group information of each user, but the problem is I have an additional query for each row, this is not good, I think that when with join I can only do this one. Of course, now we have to map this result to an object. so in my abstract Mapper class i am trying to load loadable tables for each parent row using column aliases (similar to Yii .. i think) so i get in one request a value object like this // user model object
$userMapper= new UserMapper();
$users= $userMapper->fetchAll(); //Array of user objects
echo $user->id;
echo $user->getGroup()->name // $user->getParentModel('group')->name // this info is already in the object so no extra query is required.
I think you get my gist ... Is there a custom solution of your own, perhaps more academic than mine, to do this without avoiding multiple queries? // Zend db table makes additional queries to get metadata that is good and can be cached. My problem is to get information about the parent row ... for example in yii ... something like this $ userModel-> with ('group') -> fetchAll (); Thank.
source to share
Create your cartographer to work with Zend_Db_Select. This should provide the necessary flexibility. Whether the group table is grouped depends on the option provided to the mapping methods, in this example the group object is the critical option.
class Model_User {
//other fields id, username etc.
//...
/**
* @var Model_Group
*/
protected $_group;
public function getGroup() {
return $this->_group;
}
public function setGroup(Model_Group $group) {
$this->_group = $group;
}
}
class Model_Mapper_User {
/**
* User db select object, joins with group table if group model provided
* @param Model_Group $group
* @return Zend_Db_Select
*/
public function getQuery(Model_Group $group = NULL) {
$userTable = $this->getDbTable('user'); //mapper is provided with the user table
$userTableName = $userTable->info(Zend_Db_Table::NAME); //needed for aliasing
$adapter = $userTable->getAdapter();
$select = $adapter->select()->from(array('u' => $userTableName));
if (NULL !== $group) {
//group model provided, include group in query
$groupTable = $this->getDbTable('group');
$groupTableName = $groupTable->info(Zend_Db_Table::NAME);
$select->joinLeft(array('g' => $groupTableName),
'g.group_id = u.user_group_id');
}
return $select;
}
/**
* Returns an array of users (user group optional)
* @param Model_User $user
* @param Model_Group $group
* @return array
*/
public function fetchAll(Model_User $user, Model_Group $group = NULL) {
$select = $this->getQuery();
$adapter = $select->getAdapter();
$rows = $adapter->fetchAll($select);
$users = array();
if (NULL === $group) {
foreach ($rows as $row) {
$users[] = $this->_populateUser($row, clone $user);
}
} else {
foreach ($rows as $row) {
$newUser = $this->_populateUser($row, clone $user);
$newGroup = $this->_populateGroup($row, clone $group);
//marrying user and group
$newUser->setGroup($newGroup);
$users[] = $newUser;
}
}
return $users;
}
/**
* Populating user object with data
*/
protected function _populateUser($row, Model_User $user) {
//setting fields like id, username etc
$user->setId($row['user_id']);
return $user;
}
/**
* Populating group object with data
*/
protected function _populateGroup($row, Model_Group $group) {
//setting fields like id, name etc
$group->setId($row['group_id']);
$group->setName($row['group_name']);
return $group;
}
/**
* This method also fits nicely
* @param int $id
* @param Model_User $user
* @param Model_Group $group
*/
public function fetchById($id, Model_User $user, Model_Group $group = NULL) {
$select = $this->getQuery($group)->where('user_id = ?', $id);
$adapter = $select->getAdapter();
$row = $adapter->fetchRow($select);
$this->_populateUser($row, $user);
if (NULL !== $group) {
$this->_populateGroup($row, $group);
$user->setGroup($group);
}
return $user;
}
}
use cases
/**
* This method needs users with their group names
*/
public function indexAction() {
$userFactory = new Model_Factory_User();
$groupFactory = new Model_Factory_Group();
$userMapper = $userFactory->createMapper();
$users = $userMapper->fetchAll($userFactory->createUser(),
$groupFactory->createGroup());
}
/**
* This method needs no user group
*/
public function otherAction() {
$userFactory = new Model_Factory_User();
$userMapper = $userFactory->createMapper();
$users = $userMapper->fetchAll($userFactory->createUser());
}
Greetings
source to share
I wrote a solution by subclassing Zend_Db_Table_Rowset_Abstract
and Zend_Db_Table_Row_Abstract
. I will try to summarize it briefly, and if it is of interest to anyone, I can expand on it.
I created an abstract model class - My_Db_Table_Row
- that contains an array (keyed by child class) of the rows of children.
I created an abstract class Rowset - My_Db_Table_Rowset
- which extracts data from a query based on column names and creates rows stored in My_Db_Table_Row_children
.
The class My_Db_Table_Rowset
uses _dependantTables
and _referenceMap
from Zend_Db_Table_Abstract
to create child instances (from the concatenated columns) and add them to the corresponding array in _children
their parent instance (created from the 'primary table').
Access to the child is performed as follows: $car->getDrivers();
public function getDrivers() {
// allow for lazy loading
if (!isset($this->_children['My_Model_Driver'])) {
$this->_children['My_Model_Driver'] = My_Model_Driver::fetch........;
}
return $this->_children('My_Model_Driver');
}
I originally coded this for 2 levels, parent and child, but I'm in the process of expanding this to handle more levels, for example. grandparent-parent-child.
source to share