One-to-many joins with Zend_Db_Table_Select – Rob Allen's DevNotes


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->joinLeft('artist', 'album.artist_id =',
array('artist_name' => 'name'));

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