ZF: Models multiple joins
Posted: Fri Jan 29, 2010 4:46 am
Hi,
I will cut down my issue for simplicity.
I have 4 tables:
Jobs
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
Test Runs
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| job_id | int(11) | YES | | NULL | |
| browser_id | int(11) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
Browsers
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| version_major | int(11) | YES | | NULL | |
| version_minor | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
Pages
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| test_run_id | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| dynamic | tinyint(1) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
I would like to join all the tables together with Jobs at the top of the tree. I can easily do it in mysql, but I would like to use the ZF models rules. I think I have the rules setup correctly, just not sure how to get the info.
Here it is when I wrote it manual:
This is what I have attempted to do...
But I get an error 'fatal error: Call to undefined method Zend_Db_Table_Rowset::findDependentRowset()', and yet I still need to join on another table shortly.
Any help much appreciated, been at it for a couple of days and thinking of giving up.
I will cut down my issue for simplicity.
I have 4 tables:
Jobs
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
Test Runs
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| job_id | int(11) | YES | | NULL | |
| browser_id | int(11) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
Browsers
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| version_major | int(11) | YES | | NULL | |
| version_minor | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
Pages
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| test_run_id | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| total | int(11) | YES | | NULL | |
| dynamic | tinyint(1) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
I would like to join all the tables together with Jobs at the top of the tree. I can easily do it in mysql, but I would like to use the ZF models rules. I think I have the rules setup correctly, just not sure how to get the info.
Here it is when I wrote it manual:
Code: Select all
public function browserList($job_id)
{
# query
$select = $this->select()
->setIntegrityCheck(false)
->from($this->_name)
->joinLeft('browsers', 'test_runs.browser_id = browsers.id', array('*'))
->joinLeft('pages', 'test_runs.id = pages.test_run_id', array('total', 'pages.id as page_id'))
->where('test_runs.job_id = ?', (int) $job_id);
$results = $this->fetchAll($select);
return $results;
}Code: Select all
# get job id
$job_id = (int) $request->getParam('job_id');
# get test run info inc. browser, page
$jobs = new Default_Model_DbTables_Jobs(array('db'=>'shard1'));
$jobDetails = $jobs->find($job_id)->current();
$testRuns = $jobDetails->findDependentRowset('Default_Model_DbTables_TestRuns');
$browserList = $testRuns->findDependentRowset('Default_Model_DbTables_Browsers');Any help much appreciated, been at it for a couple of days and thinking of giving up.