PHP / Zend Framework: Which object would handle a complex table join?

Question

I think one of the more difficult concepts to understand in the Zend Framework is how the Table Data Gateway pattern is supposed to handle multi-table joins. Most of the suggestions I've seen claim that you simply handle the joins using a $db->select()...

Zend DB Select with multiple table joins
Joining Tables With Zend Framework PHP
Joining tables wthin a model in Zend Php
Zend Framework Db Select Join table help
Zend DB Select with multiple table joins

My question is: Which object is best suited to handle this kind of multi-table select statement? I feel like putting it in the model would break the 1-1 Table Data Gateway pattern between the class and the db table. Yet putting it in the controller seems wrong because why would a controller handle a SQL statement? Anyway, I feel like ZF makes handling datasets from multiple tables more difficult than it needs to be. Any help you can provide is great...

Thanks!

Answer 1

By definition, TableData Gateway handles one table only.

ZF enforces this definition with an integrity check on Zend_Db_Table_Selects. However, the integrity check can be disabled and then you can do joins. Just create a method inside your table class to do the Join via the select object like this:

public function findByIdAndJoinFoo($id)
{
   $select = $this->select();
   $select->setIntegrityCheck(false) // allows joins
          ->from($this)
          ->join('foo', 'foo.id = bar.foo_id');

   return $this->fetchAll($select);
}

If you want to stick to the definition, you you can use some sort of Service Layer or DataMapper that knows how to handle multiple tables. These sit between the Db classes and the Controllers.

Another alternative is not to use Joins but table relationships and then lazy load dependent rowsets as needed. Of course, that's not Joins then, but multiple queries.

And finally, you can still just use Zend_Db_Statement and craft your SQL by hand:

  $stmt = $db->query(
              'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?',
              array('goofy', 'FIXED'));