Zend Table Relationship Modeling with Composite Key
- by emeraldjava
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.
