Adding additional data to select query result

Question

I am fetching data from Articles table but I want to extend returned result with some data from another table. For example:

public function getArticlesByCategoryId($category_id = 0) { 
    $select = $this->_db->select()
            ->from($this->_name)
            ->limit(5)
            ->order("pubDate DESC");

    $result = $this->_db->fetchAll($select);

    $mCategories = new Model_Categories();

    foreach($result as $row) { // as &$row doesn't work
        $category_name = $mCategories->getNameById($row["category_id"]);
        $row["category_name"] = $category_name; // this to add to $result but dunno how 
        // blah blah...
    } 

    return $result; // the new one with ...->category_name in it.
}

I hope you could understand what I am looking for. Or maybe it is better to write a single query (with joins, don't know how) and fetch all the data needed in once without calling methods from another Models?

Answer 1

This indeed looks like you should use a join. This definitely is the easiest way to solve your problem. The following query would do the trick:

$select = $this->_db->select()
        ->from($this->_name)
        ->join('category_table', 'category_table.id = ' . $this->_name . '.category_id', array('category_name'))
        ->limit(5)
        ->order("pubDate DESC");

This will add the category name to the row.

---

In case you don't want to use a join, you can add a custom field to your row by using a custom row class. This however requires a bit more work. Create the class as follows:

class MyApp_Model_Row_MyRow extends Zend_Db_Table_Row_Abstract 
{
     public $categoryName;
}

Then you should indicate in your DbTable class that you want to use this new row class:

class MyApp_Model_DbTable_Articles extends Zend_Db_Table_Abstract
{
    ...
    protected $_rowClass = 'MyApp_Model_Row_MyRow';
}

You can then set the category name in a fetched row.