Page 1 of 1

ZF: Models multiple joins

Posted: Fri Jan 29, 2010 4:46 am
by jaoudestudios
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:

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;
    }
This is what I have attempted to do...

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');
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. :banghead:

Re: ZF: Models multiple joins

Posted: Sun Jan 31, 2010 3:52 am
by jaoudestudios
No one seems to have an answer in any of the forums I posted in...hmmmm is this a limitation of ZF? I have decided to use Doctrine for the database abstraction layer. Integrating it into ZF was not too bad (ish), but the power of Active records is great, I definitely recommend using Zend Framework with Doctrine. From what I understand in the near future this will be seamlessly integrated together. Hope that helps others.