Using JOIN within the Zend Framework

I found documentation very sparse on the subject of using JOIN with the Zend Framework. So i set out on a quest of many hours figuring out how to get it to work. Here is what I ended up with.

I do not claim that this is the best way to do it, or that it is correct, but here is how I solved this and got JOIN working within Zend Framework.

/application/model/Asset.php
 
class Asset extends Zend_Db_Table 
{
    private $dbAdapter;
 
    public function __construct()
    {
        $this->dbAdapter= Zend_Registry::get('dbAdapter');
    }
 
    public function getAssetInfo()
    {
        $select = new Zend_Db_Select($this->dbAdapter);
 
        $select->from(array('a' => 'asset'), '*');
 
        $select->join(array('al' => 'asset_locations'), 
                            'al.id = a.asset_locations_id');
 
	$select->join(array('ac' => 'asset_categories'), 
                            'ac.id = a.asset_categories_id');
 
	$select->where('a.statuses_id <> 5');
 
	$stmt = $select->query();
 
	$result = $stmt->fetchAll();
 
	return $result;
    }
}

/application/controllers/AssetController.php
 
public function indexAction()
{
    // instantiate the classes, with Zend_Db_Table		
    $allAssetInfo = new Asset();
 
    $this->view->allAssetInfo = $allAssetInfo->getAssetInfo();
}

/application/views/scripts/asset/index.phtml
 
<table>
    <?php foreach($this->allAssetInfo as $asset) : ?>
    <tr>
        <td><?php echo $this->escape($asset['asset_categories_name']); ?></td>
        <td><?php echo $this->escape($asset['city']); ?></td>
        <td><?php echo $this->escape($asset['asset_name']); ?></td>
    </tr>
    <?php endforeach; ?>
</table>

By Adam

3 replies on “Using JOIN within the Zend Framework”