Zend JOIN clause return as array in the object?

Question

I currently am using the following code

    $select = $this->select()
                 ->setIntegrityCheck(false)
                 ->from(array('st' => $this->_name))
                 ->join(array('sp' => 'staff_permissions'), 'sp.staff_id = st.id and sp.pool_id = ' . $pool_id )
                 ->join(array('p' => 'permissions'), 'p.id = sp.permission_id')
                 ->where('staff_id = ?', $staff_id);
    return $this->fetchAll($select)->toArray();

It combines three tables and returns the result. The 'st' table corresponds to one staff (so one row), and the other two tables correspond to multiple rows. So what I was hoping was to get a single object back such that the other two tables are arrays inside the object.

So as an example, I get back $row, so that $row->first_name is the name, but $row->permission_id is an array with all the ids in it.

Can that be done using the JOIN clause?

Eddie Jaoude · Accepted Answer · 2013-06-20 04:37:21Z

This query should be done in the lowest layer in your application, the next layer up the stack will be your mappers. In your mapper layer you can map to your Entities, which will be a 'staff' entity (object) which contains a collection for 'staff_permissions' & a collection for 'permissions'

model diagram:

-----------
| service | // business logic
-----------
     |
-----------
|  mapper | // maps external data to internal entities (or vice versa)
-----------
     |
-----------    ----------------------
|dao (sql)| -> | zend table gateway |
-----------    ----------------------

mapper example code:

$staffEntity = new StaffEntity();
$staffEntity->setName($response['name']);

foreach($response['staff_permissions] as $permission) {
    $permission = new Permission();
    $permission->setName($permission['name']);
    $permission->setRule($permission['rule']);
    // ... etc ...
    $staffEntity->addPermission($permission);
}

// ... same for permissions ...

return $staffEntity;

Collectives™ on Stack Overflow

Zend JOIN clause return as array in the object?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related