Zend_Db_Select multiple table joins explained

It sounds like a simple task - retrieve the result from a join SQL query. Unusually you can even find documentation on the official Zend Framework site explaining how to put together a query that will return the results from a JOIN query. Unfortunately when it actually comes to putting theory into practice any Zend newcomer can run into several problems.

You Think It’s Easy…

For starters rather than use “raw” Zend_Db_Select:

$select = $db->select();

I wanted to derive the select from my Zend_Db_Table_Abstract, like so:

class Model_Db_Website extends Zend_Db_Table_Abstract {
	(...)
	public function fetchWebsites(){
		$select = $this->select();
		$select->join(array('whi' => 'website_has_images), 'whi.website_id = websites.website_id')
		return $this->fetchAll($select);
	}
}

This was more or less the example I found in the documentation. You might be wondering what is wrong with this picture. At first it seems like there is nothing missing. The table fields and the table name (for the FROM clause) are taken from the table class, and we have provided all the necessary JOIN details… However instead of the expected result, you an error message!

Message: Select query cannot join with another table 

The Solution

Not to prolong any more here is the final bit, which we will walk-through below.

class Model_Db_Website extends Zend_Db_Table_Abstract {
	(...)
	public function fetchWebsites(){
		$select = $this->select();
		$select->setIntegrityCheck(false)
			->from($this->_name, '*')
			->join(
				array('whi' => 'website_has_images'),
				'whi.website_id = websites.website_id', '*'
    			);
		return $this->fetchAll($select);
	}
}

The first thing you need to do is get rid of the error. This one has been mentioned online quite a lot and is easy to fix by applying:

->setIntegrityCheck(false)

The code above makes your result set read only, but allows you to do joins.

Now for the less obvious bits. Once the above problem is fixed you will notice that your results only include the join table fields. To fix this add the final parameter to the list - either a ‘*’ or an explicit list of fields.

Many sites suggest using an empty array, though this causes the result set to return only main table fields…

The ‘*’ is not enough though. You still need the from declaration ->from($this->_name, ‘*’), as without you will still be getting just the join table fields.

So there you go, it took me quite a bit of fiddling and randomly changing the parameter set to arrive at the final solution. Hope it saves you the hassle too!

You can leave a response, or trackback from your own site.