Five Minute Guide: Join data from two tables in one collection using filters

Let’s say you have related information in two separate tables in Magento, e.g. news articles and authors. You can get a collection of news items easily enough with:
Mage::getModel('news/articles')->getCollection()
But how can you get the author information in the same collection? Here’s where collection filters come in handy. They basically allow you to add joins to a collection.

In your Block class, instantiate the collection and call the filter, passing the author ID:

class News_Block_Article_List extends Mage_Core_Block_Template {

/**
* Get all articles associated with author
*
* @param $authorId
*/
public function getArticles( $authorId ) {
return Mage::getModel( 'news/articles' )->getCollection()->addAuthorFilter( $authorId );
}
}

Then define the addAuthorFilter() method in your Resource Model:


class News_Model_Resource_Article_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract {

/**
* Filter news articles by author
* @param $authorId
*
* @return $this
*/
public function addAuthorFilter( $authorId ) {
$this ->addFilter( 'author_id', array( 'eq' => $authorId), 'public');

return $this;
}

/**
* Hook for operations before rendering filters
*/
protected function _renderFiltersBefore() {
if ( $this->getFilter( 'author_id' ) ) {
$this->getSelect()->join(
array( 'na' => $this->getTable( 'news/authors' ) ),
'main_table.author_id=na.id',
array( '*' )
);
}
}
}