CODEWITHSUNDEEP
sqlzend-frameworkjoin
user1083320
user1083320

Reputation: 1946

Zend JOIN clause return as array in the object?

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?

Upvotes: 0

Views: 103

Answers (1)

Eddie Jaoude
Eddie Jaoude

Reputation: 1708

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;

Upvotes: 1

Related Questions