Issues

ZF-7808: Missing or incorrect aliases for columns in 'intersectionTable' and 'matchTable' when using findManyToManyRowset() method

Description

I have 3 models named Article, Category, ArticleCategories extending Zend_Db_Table_Abstract. The Article and the Category Model override the select() Method by adding a condition. This condition includes a column named 'deleted' in both db tables which causes the major problem.

This is the working sql query without the conditions: 'SELECT categories.categories_id, categories.categories_name, categories.categories_description, i.*, m.* FROM categories INNER JOIN categories2articles AS i INNER JOIN articles AS m ON i.articles_id = m.articles_id WHERE (i.categories_id = 1)'

By adding the condition I get: 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: Column 'deleted' in where clause is ambiguous'

This is, of course, a normal behaviour, since both sql strings for the conditions are the same, nameley: 'AND deleted IS NULL'.

Initially I try specify the columns. F.e. by adding the db table names as aliases which leads me to:

'AND categories.deleted IS NULL' // $select->where($this->_name . '.deleted ?', new Zend_Db_Expr('IS NULL')); and 'AND articles.deleted IS NULL' // $select->where($this->_name . '.deleted ?', new Zend_Db_Expr('IS NULL'));

The problem remains since the findManyToManyRowset() method creates its own aliases 'i' for '$intersectionTable' and 'm' for '$matchTable'.

I could hardcode these aliases in the models select() methods by: $select->where('m.deleted ?', new Zend_Db_Expr('IS NULL')); $select->where('i.deleted ?', new Zend_Db_Expr('IS NULL'));

But this will obviously cause problems with my table abstraction sooner or later.

Shouldn't the method simply add the correct columns to the correct aliases when calling the models table definition / override select() method?

<?php $category = new Category(); $categoriesRowset = $category->fetchAll(); /** * @return 'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: Column 'deleted' in where clause is ambiguous' */ foreach ($categoriesRowset as $categoryRow) { $articlesRowset = $categoryRow->findManyToManyRowset('Article', 'ArticleCategories', null, null ); } ?> <?php class Article extends Zend_Db_Table_Abstract { protected $_name = 'articles'; protected $_primary = 'articles_id'; protected $_referenceMap = array( 'ArticleCategories' => array( 'columns' => 'articles_id', 'refTableClass' => 'ArticleCategories', 'refColumns' => 'categories_id' ) ); public function select() { $select = parent::select(); $select ->where('deleted ?', new Zend_Db_Expr('IS NULL')) ; /** * @return SELECT `articles`.* FROM `articles` WHERE (deleted IS NULL) */ return $select; } } ?> <?php class Category extends Zend_Db_Table_Abstract { protected $_name = 'categories'; protected $_primary = 'categories_id'; protected $_dependentTables = array('ArticleCategories'); public function select() { $select = parent::select(); $select ->from('categories', array( 'categories_id', 'categories_name', 'categories_description' )) ->where('deleted ?', new Zend_Db_Expr('IS NULL')) ; /** * @return SELECT `categories`.`categories_id`, `categories`.`categories_name`, `categories`.`categories_description` FROM `categories` WHERE (deleted IS NULL) */ return $select; } ?> <?php class ArticleCategories extends Zend_Db_Table_Abstract { protected $_name = 'categories2articles'; protected $_primary = array('articles_id', 'categories_id'); protected $_referenceMap = array( 'Article' => array( 'columns' => 'articles_id', 'refTableClass' => 'Article', 'refColumns' => 'articles_id' ), 'Category' => array( 'columns' => 'categories_id', 'refTableClass' => 'Category', 'refColumns' => 'categories_id' ) ); ?>

Comments

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.