Fetch parent row automatically

[Ralf Eggert]

Hi,

I am currently using the Zend_Db_Table Relationships and all works very
nice. But there is one thing I am really missing.

With fetchAll I am reading a list of jobs from the database. Each job
has an category_id which is referenced to a category table. When I want
to output the list of jobs, I need to use findParentGroup() for each row
of the rowset which is a really pain, when outputing 50 rows.

How do others handle this problem? Is there an easy way how to amend
Zend_Db_Table or Zend_Db_Table_Rowset to get the related data for each
Zend_Db_Table_Row object?

Thanks in advance for any advise.

Best regards,

Ralf

[Hector Virgen]

If you have a one-to-many relationship with the categories (it appears you do), you can join the category table in your select query to return all the data you need in one shot.

<?php

$jobs = new Jobs(); // jobs table
$categories = new Categories(); // categories table

$select = $jobs->select()
->join($categories, 'jobs.category_id = categories.id');

$rows = $jobs->fetchAll($select); // returns rowset of jobs with category info

?>

-Hector

Ralf Eggert wrote:

Hi,

I am currently using the Zend_Db_Table Relationships and all works very
nice. But there is one thing I am really missing.

With fetchAll I am reading a list of jobs from the database. Each job
has an category_id which is referenced to a category table. When I want
to output the list of jobs, I need to use findParentGroup() for each row
of the rowset which is a really pain, when outputing 50 rows.

How do others handle this problem? Is there an easy way how to amend
Zend_Db_Table or Zend_Db_Table_Rowset to get the related data for each
Zend_Db_Table_Row object?

Thanks in advance for any advise.

Best regards,

Ralf


  

[Ralf Eggert]

Hi Hector,

thanks for your suggestion. Unfortunately, I did not get it to run. But
I found another solution that works fine for me.

In my Jobs class (derived from Zend_Db_Table_Abstract) I overwrite the
_fetch() method to add the join.

protected function _fetch(Zend_Db_Table_Select $select)
{
    // add join for category
    $select->__toString();
    $select->join('jobcategories', 'job_category_id = category_id',
                  array('category_name'))
           ->setIntegrityCheck(false);

    // fetch data
    $stmt = $this->_db->query($select);
    $data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
    return $data;
}

This is not working without the call of $select->__toString() before
adding the join. Otherwise the join() call is overwriting the current
select partially. And the ->setIntegrityCheck(false) method call is
needed to avoid the "Select query cannot join with another table" warning.

Another small change is needed in my Job class (derived from
Zend_Db_Table_Row_Abstract) because the Job objects are set to readonly
while instantiated. So I had to add the call of
$this->setReadOnly(false) in my Job::init() method. Otherwise the
updating of a Job object is not possible due to the error message
"Zend_Db_Table_Row_Exception: This row has been marked read-only".

Hope this helps someone else.

Best Regards,

Ralf