One-to-many joins with Zend_Db_Table_Select
Let’s say that you want to set up a one-to-many relationship between two tables: Artists and Albums because you’ve refactored my ZF1 tutorial.
Let’s assume that an artist has many albums. These are the basic table definitions:
artists table: id, artist
albums table: id, artist_id, title
When you list the albums, you obviously want to see the artist name rather than the id, so clearly you use a join!
Assuming you’re using Zend_Db_Table, the easiest way is to turn off the integrity check and do a join in a mapper or table method.
Something like this:
class AlbumTable extends Zend_Db_Table_Abstract
{
protected $_name = 'album';
public function fetchAllWithArtistName($order = array('title ASC'))
{
$select = $this->select();
$select->setIntegrityCheck(false);
$select->from($this->_name);
$select->joinLeft('artist', 'album.artist_id = artist.id',
array('artist_name' => 'name'));
$select->order($order);
$rows = $this->fetchAll($select);
return $rows;
}
}
The row set returned will have all the columns from the albums table and one additional column called artist_name which is an alias of the name column from the artists table.
It's worth mentioning that you can do the same query with chaining, if you're being economic with the keypresses :-)
$select->from()->where()->order()->limit();
I love Zend Db, so many people prefer Doctrine though. I'm yet to find a need for it!
You have no idea how long "$select->setIntegrityCheck(false);" evaded me. I all but gave up thinking a join simply wasn't possible until I found that little snippet.
Well, from a irc chat with @bittarman, he stresses that setIntegrityCheck is – and I quote – "a hack". It actually returns "broken" row objects. And I agree with him in that you could just use the adapter and get the result set as an array.
Mind you, I use setIntegrityCheck quite frequently.
First – let's use some wrinky-ding SQL wrapper ( create a problem for ourselves ) and then heroically overcome it. Mission accomplished.
Yes better just use the db adapter..
$select = $this->getDefaultAdapter->Select();
And make the query exactly like you want. And you do not get a fake db table rowset.
Nice, would be cool to see an article on the new Zend_Db that has been cooked up.
You said that turning off the integrity check is the easiest way… I thought I was about to find out how to do it automatically using the table relationships I defined… still not sure how they're used or why :D