Zend Framework - ORM relationships and optimization

Question

I've been using ZF for few months and I'm really happy with it however I'm not completely sure about how to work with models relationships and at the same time avoid multiple queries to the db. Many people has this problem and no one seems to find a good solution for it. (and avoiding using a third party ORM) For example I have a list of users, and each user belongs to a group. I want a list of users displaying user info and group name (to tables: users, and groups. Users has a foreign key to the table groups). I have: 2 mapper classes to handle those tables, UserMapper and GroupMapper. 2 Model Classes User and Group 2 Data Source classes that extends Zend_DB_Table_Abstract

in user mapper I can do findParentRow in order to get the group info of each user, but the problem is i have an extra query for each row, this is not good I think when with a join I can do it in only one. Of course now we have to map that result to an object. so in my abstract Mapper class I attempt to eager load the joining tables for each parent row using column aliasing (similar as Yii does.. i think) so I get in one query 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 point... Is there a native solution, perhaps more academic than mine, in order to do this without avoiding multiple queries? // Zend db table performs extra queries to get the metadata thats ok and can be cached. My problem is in order to get the parent row info... like in yii.... something like that $userModel->with('group')->fetchAll(); Thank you.

I struggle with the same issue. I think the core problem is that `Zend_Db_Table` is a TableDataGateway (TDG) implementation. It is flexible enough to deal with linked tables, but without custom queries employing joins, you end up with multiple queries as you have identified. It's precisely this mismatch between TDG and my real-world needs that often push me towards third-party ORM's like Doctrine. — David Weinraub, Mar 19 '12 at 11:00
Exactly, but Doctrine is too big for the projects I usually work with. , so I try to rely just in Zend classes whenever is possible, and keep it simple. I don't know what are the plans for Zend_db and related classes in ZF2, but I hope we can have some improvements... ZF2 should be released this Summer/Autumn I think — FranStrentz, Mar 19 '12 at 11:42
The ZF2 `Zend\Db\Table` API will probably be functionally the same as for ZF1 `Zend_Db_Table`; both are, after all, TDG implementations. Sadly, it's probably not reasonable to expect ZF2 to resolve this fundamental TDG/mapper/join/use-case tension. — David Weinraub, Mar 20 '12 at 05:49

score 2 · Answer 1 · edited Oct 19 '12 at 21:12

I have written a solution by subclassing Zend_Db_Table_Rowset_Abstract and Zend_Db_Table_Row_Abstract. I'll try to summarise it briefly and if it is of interest to any one I can expand on it.

I created an abstract model class - My_Db_Table_Row - that contains an array (keyed on child classname) of rowsets of children.

I created an abstract Rowset class - My_Db_Table_Rowset - that extracts the data from a query based on column names and creates rowsets stored in My_Db_Table_Row_children.

The My_Db_Table_Rowset class uses _dependantTables and _referenceMap from Zend_Db_Table_Abstract to create child instances (from joined columns) and add them to the appropriate array within the _children of their parent instance (created from 'primary table' columns).

Accessing a child is done 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');
}

Initially, I coded this for 2 levels, parent and child but I am in the process of extending this to handle more levels, e.g. grandparent-parent-child.

Weltschmerz · Accepted Answer · 2012-03-20T15:53:00.087

Develop your mapper to work with Zend_Db_Select. That should allow for flexibility you need. Whether group table is joined depends on the parameter provided to mapper methods, in this example group object is the critical parameter.

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 scenarios

/**
 * 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());
}

Cheers

Hi, thanks for answering. I think I'm doing something like you describe. My mappers extends from an abstract class, that class has common methods such as fetch all, in the abstract class I pass the name of the data table source that extends zend db table abstract so I can have access to the table names and relations using the _referenceMap info. Having this and the name of the model class Im intend to map I can do the mapping in one join using aliasing. It works fine for my purposes (a custom and simple CMS)... — FranStrentz, Mar 21 '12 at 12:28
...but when I was writing that I was kind of asking to myself if this functionality could have been added to the framework. (mapping objects to related tables) But Well I think is also part of ZF philosophy "Models/mappers" is your job ;) — FranStrentz, Mar 21 '12 at 12:32
Someone write an abstract class fed by every model's $_referenceMap so we don't have to write that much for Zend's sake! — Mauro, Apr 12 '12 at 20:39

Zend Framework - ORM relationships and optimization

2 Answers2