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.
February 8th, 2012 at 09:29 #
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!
February 8th, 2012 at 14:02 #
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.
February 8th, 2012 at 18:30 #
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.
February 9th, 2012 at 01:43 #
First - let's use some wrinky-ding SQL wrapper ( create a problem for ourselves ) and then heroically overcome it. Mission accomplished.
February 10th, 2012 at 19:54 #
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.
February 26th, 2012 at 11:34 #
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