Zend Table Relationship Modeling with Composite Key

Posted by emeraldjava on Stack Overflow See other posts from Stack Overflow or by emeraldjava
Published on 2010-05-28T17:49:07Z Indexed on 2010/05/28 17:51 UTC
Read the original article Hit count: 268

Filed under:

I have a table with a composite primary key using four columns.

mysql> describe leaguesummary;
+------------------------+------------------+------+-----+---------+----------------+
| Field                  | Type             | Null | Key | Default | Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| leagueid               | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| leaguetype             | enum('I','T')    | NO   | PRI | NULL    |                | 
| leagueparticipantid    | int(10) unsigned | NO   | PRI | NULL    |                | 
| leaguestandard         | int(10) unsigned | NO   |     | NULL    |                | 
| leaguedivision         | varchar(5)       | NO   | PRI | NULL    |                | 
| leagueposition         | int(10) unsigned | NO   |     | NULL    |                | 

I have the league object modelled as so (all plain enough mappings)

<?php
class Model_DbTable_League extends Zend_Db_Table_Abstract
{
    protected $_name = 'league';
    protected $_primary = 'id';
    protected $_dependentTables = array('Model_DbTable_LeagueSummary');

And I've started like this on the new model class. I've mapped a simple reference map which returns all rows linked to the league id.

// http://files.zend.com/help/Zend-Framework/zend.db.table.relationships.html
// http://naneau.nl/2007/04/21/a-zend-framework-tutorial-part-one/
class Model_DbTable_LeagueSummary extends Zend_Db_Table_Abstract {

    protected $_name = "leaguesummary";
    protected $_primary = array('leagueid', 'leaguetype','leagueparticipantid','leaguedivision');

    protected $_referenceMap = array(
        'Summary' => array(
            'columns'           => array('leagueid'),
            'refTableClass'     => 'Model_DbTable_League',
            'refColumns'        => array('id')
            ),
            .....
        );
}
?>

The simple case works when called from my controller

public function listAction()
{
    // action body
    $leagueTable = new Model_DbTable_League();
    $this->view->leagues = $leagueTable->getLeagues();

    $league = $leagueTable->getLeague(6);

    // work
    $summary = $league->findDependentRowset('Model_DbTable_LeagueSummary','Summary');
    Zend_Debug::dump($summary,"",true);

I'm not sure how i can define extra _referenceMap keys which will take extra contraint ket values. I would like to be able to define a set called 'MenA' in which the type and division values are hardcoded, and the league id is taken from the initial rowset.

        'MenA' =>array(
            'columns'           => array('leagueid','leaguetype','leaguedivision'),
            'refTableClass'     => 'Model_DbTable_League',
            'refColumns'        => array("id","I","A")
            )

Is this style of mapping possible ie hardcoding the values into the 'refColumns'. The second crazy idea i had was to pass the variable values in as part of the third param of the findDependentRowset() method.

    $menA = $league->findDependentRowset('Model_DbTable_LeagueSummary','MenA',array("I","A"));

Any suggestions on how I might use the Zend DB Table Relationship mapping correctly to do this would be appreciated. I'm not interested in the plain, old and ugly $db->select(a,b,c)->where(..) style solution.

© Stack Overflow or respective owner

Related posts about zend-framework