R – To get the complete row with join tables in Zend_Db_Table

zend-dbzend-db-tablezend-framework

This is my table structure.

http://img6.imageshack.us/img6/8730/articlek.jpg

I want to get a article row object from id with section and category names instead of section_id and category_id ,And user names instead of author_id and modified_by.

Please help me.

Best Answer

You need to use Zend_Db_Table's setIntegrityCheck() functionality:

Something like this should work:

$articleTable = new Article();
$select = $articleTable->select();
$select->setIntegrityCheck(false);
$select->from('article', 'article.*');
$select->joinLeft('user', 'article.author_id = user.id', array('author_name'=>'author'));
$select->where('article.id = 123');
$articleRecord = $articleTable->fetchRow($select);

You can check the SQL generated using:

Zend_Debug::dump($select->__toString());

Note that the returned Zend_Db_Table_Row object is read only.

Related Solutions

Mysql – How to do a joined query in the ZF tables interface

$models = new Models();
$select = $models->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
       ->join(array("a"=>"manufacturers"), 'models.manufacturer_id = a.id',
         array("man_name"=>"name", "man_description"=>"description"))
       ->where("a.name LIKE 'A%'");
$rowset = $models->fetchAll($select);

Unfortunately the Zend_Db_Table relationships interface doesn't have much intelligence in it related to creating joined queries from its declared reference map. The community-contributed solution for complex queries is the Zend_Db_Table_Select query factory.

Note you have to give column aliases for manufacturer's name and description, or else these columns will suppress the model's name and description in the associative array for the row data. You should name columns distinctly to avoid this.

But in your case, I'd skip the table interface and the select interface, and simply execute an SQL query directly using the Db adapter:

$data = $db->fetchAll("
  SELECT m.*, a.name AS man_name, a.description AS man_description
  FROM Models m JOIN Manufacturers a ON m.manufacturer_id = a.id
  WHERE a.name LIKE 'A%'");

You'll get the data back as a simple array of associative arrays, not as a Zend_Db_Table_Rowset. But since a joined rowset isn't writeable anyway, you haven't sacrificed much.

Best Answer

Related Solutions

Mysql – How to do a joined query in the ZF tables interface

Related Topic