$_referenceMap Relationships with Zend_Db_Table_Abstract creates too many queries

Question

This is my first time using Zend Framework for an application and i don't know if I completely have by head around Models.

I have four tables: shopping_cart, product, product_unit, distributor. shopping cart has an cart_id, product_id, unit_id and dist_id (shopping cart joins on the other tables with their corresponding id).

Before Zend I would create a class like this:

class ShoppingCart
{
function getItems()
{
$sql ="select * from shopping_cart, product, product_unit, distributor 
where 
shopping_cart.product_id = product.id AND
shopping_cart.unit_id = product_unit.id AND  
shopping_cart.dist_id = distributor.id AND
cart_id = xxx";

$items = $this->db->getAll($sql);
}

One query to get all the information from the joined tables.

When I set up the relationship mapping in Zend_Db_Table_Abstract:

My Shopping Cart Model:

class Application_Model_ShoppingCart
{
    function __construct()
    {
        $this->ShoppingCartTable = new Application_Model_DbTable_ShoppingCart();
    }

    function getItems()
    {
        $cart_items = $this->ShoppingCartTable->getItems($this->GetCartId());
        return $cart_items;
    }
}

class Application_Model_DbTable_ShoppingCart extends Zend_Db_Table_Abstract {

protected $_name = 'shopping_cart';

protected $_rowClass = 'Application_Model_DbTable_ShoppingCart_Item';

protected $_referenceMap    = array(
    'Product' => array(
        'columns'           => 'product_id',
        'refTableClass'     => 'Application_Model_DbTable_Product',
        'refColumns'        => 'id'
        ),
    'Distributor' => array(
        'columns'           => 'dist_id',
        'refTableClass'     => 'Application_Model_DbTable_Distributor',
        'refColumns'        => 'id'
        ),
    'Unit' => array(
        'columns'           => 'unit_id',
        'refTableClass'     => 'Application_Model_DbTable_ProductUnit',
        'refColumns'        => 'id'
        )
);

public function getItems($cart_id)
{
    $where = $this->getAdapter()->quoteInto('cart_id = ?', $cart_id);

    return $this->fetchAll($where);
}

}

In my controller:

$this->_shoppingCartModel = new Application_Model_ShoppingCart();
$items = $this->_shoppingCartModel->getItems();

IN my view :

foreach($this->items AS $item) 
{
    $item_product = $item->findParentRow('Application_Model_DbTable_Product');
    $item_dist = $item->findParentRow('Application_Model_DbTable_Distributor');
    $item_unit = $item->findParentRow('Application_Model_DbTable_ProductUnit');
}

when I have ten items in my cart the db profiler shows over sixty queries (WHOA) to view the cart items ( information across all four tables are displayed - product name, unit description, distributor name). For each item it queries the shopping_cart, then querys the product table, then the product unit, then the distributor_table.

Is there a way to have this run as one query joining all the tables via Zend_Db_Table_Abstract relationships?
Will I have to go back to using db adapter in the my Application_Model_ShoppingCart class?

I want to abstract all data access to the table models (Application_Model_DbTable_ShoppingCart) and not have the Application_Model_ShoppingCart tied to a db handler.

Thanks in advance for advice, I love the Zend Framework but models are still hard for me to understand given the different conflicting ways people talk about using them.

Answer 1

In short, no, unfortunately it's not possible to get a set of table rows together with their relationships in a single query.

It's just that all methods dealing with relationships are defined for a row, not for a table.

But at least, you can form your sql with Zend_Db_Table_Select instead of writing it all manually.

Upd: Your code for fetching ShoppingCarts, in my opinion, should belong to the table (DbTable_ShoppingCart). So, the code you provided in the beginning could be transformed to the following:

class Application_Model_DbTable_ShoppingCart extends Zend_Db_Table_Abstract {
   public function getItem($cart_id) {
      $select = $this->select()
                     ->from( array('sc' => 'shopping_cart'), array(Zend_Db_Select::SQL_WILDCARD) )
                     ->join( array('p' => 'product'), 'sp.product_id = p.id', array(Zend_Db_Select::SQL_WILDCARD) )
                     ->join( array('pu' => 'product_unit'), 'sp.unit_id = pu.id', array(Zend_Db_Select::SQL_WILDCARD) )
                     ->join( array('d' => 'distributor'), 'sp.dist_id = d.id', array(Zend_Db_Select::SQL_WILDCARD) )
                     ->where('sp.cart_id = ?', $cart_id)
                     ->setIntegrityCheck(false);
      return $this->fetchAll($select);
   }
}