ZF-3485: Zend_Db_Table_Select with join fails
Description
Because of the problem described in ZF-2546, joins will fail on a Zend_Db_Table_Select:
$categoryTable = new CategoryTable(array(
'db' => 'db',
'name' => 'category',
'primary' => 'id'));
// Get categories belonging to a product--
// A many-to-many relationship
$select = $categoryTable->select()
->join('category_product',
'category_product.category_id = category.id',
array())
->where('category_product.product_id=?', 123);
echo "\$select: ", $select, "\n";
// SELECT `category`.* FROM `category_product`
// INNER JOIN `category` WHERE (category_product.product_id='123')
// WRONG:
// 1) Incorrect table in 'FROM'
// 2) Missing 'ON' clause in 'INNER JOIN'
// The workaround:
// This time, include a from()
$select = $categoryTable->select()
->from('category')
->join('category_product',
'category_product.category_id = category.id',
array())
->where('category_product.product_id=?', 123);
echo "\$select: ", $select, "\n";
// SELECT `category`.* FROM `category`
// INNER JOIN `category_product` ON category_product.category_id=category.id
// WHERE (category_product.product_id='123')
// *CORRECT*
Comments
Posted by Ralph Schindler (ralph) on 2009-05-19T12:11:51.000+0000
This was fixed in part by the feature added in ZF-2798.
To make your join queries work, use this logic:
$table->select(Zend_Db_Table_Abstract::SELECT_WITH_FROM_PART)->setIntegrityCheck(false);
The documentation was also updated.