Using Multiple Tables in a Zend Model and returning a combined result set

Question

Hi This is either a very specific or very generic quetion - I'm not sure, and I'm new to the Zend framework / oo generally. Please be patient if this is a stupid Q...

Anyway, I want to create a model which does something like:

Read all the itmes from a table 'gifts' into a row set

for each row in the table, read from a second table which shows how many have been bought, the append this as another "field" in the returned row

return the row set, with the number bought included.

Most of the simple Zend examples seem to only use one table in a model, but my reading seems to suggest that I should do most of the work there, rather than in the controller. If this is too generic a question, any example of a model that works with 2 tables and returns an array would be great!

thanks for your help in advance!

Answer 1

I would recommend creating a function in your gifts model class that returns what you want. It would probably look something like:

public function getGiftWithAdditionalField($giftId) {
  $select = $this->getAdapter()->select()
    ->from(array('g' => 'gifts'))
    ->joinLeft(array('table2' => 't2'), 'g.gift_id = t2.gift_id', array('field' => 'field'))
    ->where('g.gift_id = ?', $giftId);
  return $this->getAdapter->fetchAll($select);
}

You can check out the Zend Framework Docs on Joins for more info.

Answer 2

I assume second tables is something like "gift_order" or something.

In this case you need to specify tables relationships beetween "gift" and and "gift_order" via foreign keys and describe it in table class.

It will look like this

    class GiftOrder extends Zend_Db_Table_Abstract
    {
    /** Table name */
    protected $_name    = 'gif_order';
    protected $_referenceMap = array(
    "Fileset" =>array(
     "columns" => array("gifId"),
     "refTableClass" => "Gift",
     "refColumns" => array("id")
    ));
      ........................

You need to specify foreigh key constraint while create table with SQL
      ALTER TABLE `gift_order`
  ADD CONSTRAINT `order_to_gift` FOREIGN KEY (`giftId`) REFERENCES `gift` (`id`) ON DELETE CASCADE;

If this is something you looking for you could find more on this at this link link http://framework.zend.com/manual/en/zend.db.table.relationships.html

With such solution you will be able to loop gifts and get their orders without any complex SQL's

$rowSetGifts = $this->findGifts();

while($rowSetGifts->next()){
   $gift = $rowSetGifts->current();
   $orders = $gift->findGiftOrder();//This is magick methods, this is the same $gift->findDependentRowset('GiftOrder');

//Now you can do something with orders - count($orders), loop them or edit

}