zend relationships with select

I am new to zend. I have been asked to redevelop a website that was once written in plain PHP and put it into the zend framework.

I am having a lot of trouble with database relationships, I cant seem to get my head round defining and querying relationships.

I would like to find a Category. From that Category I would like to be able to find all the CategoryInfo associated with it, and be able to query/sort/limit that dataset.

Here are my models.

Categorys.php

<?php
  class Default_Model_Categorys extends Zend_Db_Table_Abstract
  {
      protected $_name = 'Categorys';
      protected $_primary = 'id';

      protected $_dependentTables = array('Default_Model_CategoryInfo');
 }
?>

CategoryInfo.php

<?php
class Default_Model_CategoryInfo extends Zend_Db_Table_Abstract
{
    protected $_name = 'Category_Info';
    protected $_primary = 'id';

    protected $_referenceMap = array(
        'Categorys' => array(
            'columns' => array('cat_id'),
            'refTableClass' => 'Default_Model_Categorys',
           'refColumns' => array('id')
        )
      );
}
?>

CategoryController.php

<?php
  class CategorysController extends Zend_Controller_Action
  {
      public function indexAction()
      {
        /*
          this should redirect to all games
        */
          return $this->_forward("index", "games");
      }

      public function categoryAction()
      {
          /*
            shows a specific category
          */
          $id = (int) $this->_request->getParam('id');
          $category = new Default_Model_Categorys();
          $this->view->category = $category->fetchRow(
              $category->select()->where('id = ?', $id)
          );

          $categoryInfo = $this->view->category->findDependentRowset('Default_Model_CategoryInfo');

      }
  }

Firstly... am I doing anything wrong?

Secondly... how do I go about querying the dependent rowset?

Solution

First, if you're searching for a category by its primary key, it's simpler to use the find() method:

$id = (int) $this->_request->getParam('id');
$category = new Default_Model_Categorys();
$this->view->category = $category->find($id)->current();

Second, to restrict or sort dependent Category_Info rows, you can use a Zend_Db_Table_Select object as an optional parameter of findDependentRowset(). Here's an example:

$select = $category->select()->where("info_type = 'PRICE'")
                             ->order("info_date")
                             ->limit(3);
$categoryInfo = $this->view->category->findDependentRowset(
    'Default_Model_CategoryInfo', null, $select);

Notice you can use any table object to create that select object. Since the "FROM" clause for that select will be set by the findDependentRowset() method, you just add other clauses and then pass it in.

PS: You don't need to declare $_dependentTables at all, unless you're going to use cascading update or cascading delete via PHP code. I recommend strongly against doing that -- it's far more efficient to let the RDBMS handle those cascading operations.

Likewise you should never have to declare $_primary if your database tables actually declare primary key constraints. Zend_Db_Table_Abstract knows how to inspect metadata to get the primary key column(s).