Zend Framework 2 – dependent rows and queries with JOIN statement

If you don’t use Doctrine ORM with your Zend Framework, then there is no automation of fetching dependent rows from a database. In Zend1 you had at least findDependentRowset method helping you manage relationships – in Zend2 all queries have to be done on your own.

Since Zend2 doesn’t use the ActiveRecord pattern for models any more, all row fetching methods should be placed in Table models. It is a good place for it, because inside Table model you have access to the Database Adapter through the TableGateway. The TableGateway of the Table model also can help you filling a RowSet with rows returned by the statement – of course if the query returns columns that match relevant object fields.

Let’s suppose we have Users and Users have Privileges. Our goal is to get all Privileges of a certain User, so in out PrivilegeTable class we create method getPrivilegesByUser. It can look like that.

namespace Auth\Model;
 
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Sql;
 
class PrivilegeTable {
    protected $tableGateway;
 
    public function __construct( TableGateway $tableGateway) {
        $this->tableGateway = $tableGateway;
    }
 
    public function fetchAll() {
        $resultSet = $this->tableGateway->select();
        return $resultSet;
    }
 
    public function find($id) {
        $id = (int) $id;
        $rowset = $this->tableGateway->select(array(
			'id' => $id
		));
        $row = $rowset->current();
        if( !$row ) {
            throw new \Exception("Could not find row $id");
        }
        return $row;
    }
 
    public function getPrivilegesByUser( $oUserRow ) {
        $oSql = new Sql( $this->tableGateway->getAdapter() );
        $oSelect = $oSql
            ->select()
            ->from( 'privileges' )
            ->columns( array('id', 'name') )
            ->join( array('uhp' => 'user_has_privileges'), 'id = uhp.idprivilege', array() )
            ->join( array('u' => 'users'), 'u.id = uhp.iduser', array() );
 
        $oResultSet = $this->tableGateway->selectWith( $oSelect );		
        return $oResultSet;
    }
 
}

The method selectWith is very helpful, because it tries to package all returned rows with model class declared during creation of an appropriate TableGateway in getServiceConfig and then populate them to RowSet.

public function getServiceConfig() {
    return array(
        'factories' => array(
             /*...*/
            PrivilegeTableGateway' => function ($sm) {
            $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
            $resultSetPrototype = new ResultSet();
            $resultSetPrototype->setArrayObjectPrototype(new Privilege());
            return new TableGateway('privileges', $dbAdapter, null, $resultSetPrototype);
        },
        /*...*/
    );
}

There is no problem event when not all columns match all fields- in that case simply some fields will be empty