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.
- Tweet
This entry was posted on Wednesday, 8th February 2012 at 08:36 and is filed under Zend Framework. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.
5 Responses to “One-to-many joins with Zend_Db_Table_Select”
-
1 James
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!
-
2 Daniel
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.
-
3 nevvermind
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. -
4 Sam
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.
-
5 CHris
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.
The views expressed in these comments are not the views of the publisher. However, we believe in the rights of others to express their legitimate views and concerns. Any legitimate complaint emailed to rob@akrabat.com will be seriously considered and the post reviewed as desirable and necessary.
Leave a Reply
Buy now!