Trouble joining tables

swiftone · #1 (**permalink**) 08-15-2007, 04:42 PM

I am building an application, and now have a need to join a couple of tables, but have not figured it out yet. Here is a bit about the application setup:[list][*]Base

config.ini
application
- controllers
  - AssetController.php
- models
  - Asset.php
- views
  - Scripts
    - asset
      - index.phtml
public_html
- index.php

My config contains all of my db connection info, and is retrieved using Zend_Config.
I have also setDefaultAdapter using Zend_Db_Table, and stored the info in the Zend_Registry as well.

In my /models/Asset.php I simply have a class that provides the name of the asset table in the DB.
In my /controllers/AssetController.php my method indexAction() needs to supply the data so that the view index.phtml can display a table of the results.

The problem I am having is that in order for me to pull complete hardware info I need to pull info from three tables. I am trying to grasp using Zend_Db_Select so that I can use JOIN in the queries, but I am totally lost.

I have found no samples of doing a JOIN, or perhaps I just don't understand it enough to see it.

Can anyone please help?

Elemental · #2 (**permalink**) 08-15-2007, 04:49 PM

Not sure if this is what your after, but check this page out.
Zend Framework: Documentation

swiftone · #3 (**permalink**) 08-15-2007, 05:23 PM

Thanks for the reply Elemental, but that is not really what I was talking about. I have found a little more info, on the Wiki, but still need a little more direction.

I am not sure if I am doing this the right way, but here is where I am to this point.

In the /model/Asset.php as a test I created an additional class Assetinfo that extends Zend_Db_Table. In the constructor I am trying to create the select by:

PHP Code:


			
$select = $this->_db->select();
$select->from('asset', '*');

Then in the controller I have:

PHP Code:


			
$allAssetInfo = new Assetinfo();

$where = 'status <> 5';

$this->view->allInfo = $allAssetInfo->fetchAll($where);

I know this doesn't work, but don't know where to go next. Once I build the SELECT, how do I execute it? Do I use fetchAll in the Zend_Db_Table?
The objective is to execute a query like this:

Code:

SELECT *
FROM asset as a 
LEFT JOIN categories as c ON c.id = a.categories_id
LEFT JOIN locations as l ON l.id = a.locations_id
WHERE a.id = $id

Then I can populate $this->view->allInfo in the controller with the result.

I could do this in many ways, but really want to keep everything portable and stay within the ZF.

Elemental · #4 (**permalink**) 08-15-2007, 07:58 PM

ok, there is implementation for joins within the Zend_Db_Select object. Documentation is scarce and I'm not sure how you would get to this object gracefully. Check out the API doc linked above.

I also found this from the mailing list.
And this from these forums.

Elemental · #5 (**permalink**) 08-15-2007, 11:19 PM

You appear to be getting Zend_Db_Select in your original post. Just curious if you got the joins to work out or not.

swiftone · #6 (**permalink**) 08-16-2007, 12:38 PM

Thanks for helping on this Elemental. I have really been pulling my hair out, and I didn't have much to begin 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

PHP Code:


			
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

PHP Code:


			
public function indexAction()
{
        // instantiate the classes, which brings in the Zend_Db_Table        
        $allAssetInfo = new Asset();
        
        $this->view->allAssetInfo = $allAssetInfo->getAssetInfo();
    }

/application/views/scripts/asset/index.phtml

HTML Code:

<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>

swiftone · #7 (**permalink**) 08-16-2007, 07:27 PM

I changed a post to show how I got JOIN to work in Zend Framework.

Elemental · #8 (**permalink**) 08-16-2007, 08:37 PM

Sweet action Jackson.

There are several proposals out there for easing the use of joins, at this point I think this is the way to do it in ZF. Hopefully something a bit less cumbersome will come about in the future.

mel · #9 (**permalink**) 03-04-2008, 10:19 AM

Just wanted to say a quick thanks for posting this - it really helped me out.

xentek · #10 (**permalink**) 03-04-2008, 04:15 PM

your getAssetInfo() method looks correct to me, given the current API for the select object.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode