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?

Config · Accepted Answer · 2012-06-23 12:56:02Z

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.

edited Jun 23, 2012 at 12:56

answered Jun 23, 2012 at 10:15

Config

1,69212 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

enenen Over a year ago

Only I get an error for setIntegrityCheck: Unrecognized method 'setIntegrityCheck()'. But even without it the result is correct. I think it is my fault because I am not sure if my whole Zend Application is configured correctly.

Config Over a year ago

Ah thanks for pointing out, my bad: setIntegrityCheck() should only be called when joining with a Zend_Db_Table_Select object. Since we call select() on the db-instance itself, a Zend_Db_Select object is created and the setIntegrityCheck() can be left out. Updated the answer. Glad it worked.

Remko · Accepted Answer · 2012-06-23 10:17:06Z

0

To get all articles with data from your category table your query could look like:

    $select = $this->_db->select()
            ->from($this->_name)
            ->joinLeftUsing('category','category_id', array('category_name'))
            ->order("pubDate DESC");

See also: http://framework.zend.com/manual/en/zend.db.select.html

answered Jun 23, 2012 at 10:17

Remko

9686 silver badges19 bronze badges

Collectives™ on Stack Overflow

Adding additional data to select query result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related