PHP / Zend Framework: which object will handle a complex table join?

Question

I think one of the most complex concepts that you need to understand in the Zend Framework is how the Table Data Gateway template should handle joining multiple tables. Most of the suggestions I've seen claim that you simply handle joins using the $ db-> select () method ...

Zend DB Select with multiple table joins
Join tables with the PHP Zend Framework
Join tables with a model in Zend Php
Zend Framework Db Choose Connect Table
Zend DB Select with multiple table joins

My question is: which object is best suited to handle this type of multiple table select statement? I feel that placing it in a model will violate the Gateway 1-1 Table Data class class and db table. However, putting it in the controller seems wrong, because why does the controller process the SQL statement? Anyway, I feel that ZF makes it easier to process datasets from multiple tables than necessary. Any help you can provide is great ...

Thanks!

Answer 1

In the definition , TableData Gateway processes only one table.

ZF sets this definition with integrity checking to Zend_Db_Table_Selects. However, integrity checking can be turned off, and then you can perform the joins. Just create a method inside your table class to do the Join via the select object as follows:

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 can use some level of service or DataMapper , which knows how to handle multiple tables. They are located between the Db classes and the controllers.

Another alternative is to use Joins, but table relationships , and then lazy load-dependent rows as needed. Of course, this is not a Union, but a few queries.

And finally, you can still use Zend_Db_Statement and create your SQL manually :

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