Re: [fw-mvc] bug with Zend_Db_Table?

Try passing in an empty array as the 3rd parameter to your join calls. The third parameter specifies which columns from the joined table to return.

->joinLeft(array('ug' => 'user_group'), 'u.id = ug.user_id', array())

--
Hector

On Wed, Jul 29, 2009 at 10:53 AM, Seth Atkins <satkins@nortel.com> wrote:

I'm seeing what I think is weird behavior since building a query with the select/where/join methods follows the real SQL syntax pretty closely. Yet the output is different. I'm getting "extra" columns that I don't want, and in some cases, unintended collisions between these unexpected column names and another column alias I created, resulting in data being overwritten in the rowset. This seems like a bug, but I'm not sure.

 

Here is the query:

 

    $query = $this->select()
          ->setIntegrityCheck(false)
          ->from(array('u' => 'user'), array('id' => 'u.id', 'username' => 'u.username', 'group_id' => 'g.id', 'group' => 'g.group'))
          ->joinLeft(array('ug' => 'user_group'), 'u.id = ug.user_id')
          ->join(array('g' => 'group'), 'ug.group_id = g.id')
          ->where('u.username = ?', $username);
    $rowset = $this->fetchAll($query);

 

I would expect 4 colums, but I get 5. If put the same query into a MySQL CLI, I get 4 columns returned like expected.

 

Raw SQL:

 

SELECT `u`.`id` AS `id`, `u`.`username` AS `username`, `g`.`id` AS `group_id`, `g`.`group` AS `group`
FROM `user` `u`
LEFT JOIN `user_group` `ug`
ON `u`.`id` = `ug`.`user_id`
JOIN `group` `g`
ON `ug`.`group_id` = `g`.`id`
WHERE `username`='Fido';

 

The extra column returned is labeled 'id' and contains the value from the group table. And that overwrites the value of the real user id column which is aliased to just 'id'. I had to actually change the alias to 'user_id' => 'u.id' to resolve this problem, but I still get that weird 5th column named just 'id' which carries the value from the group.id column.

 

I have other joined queries that exhibit the same behavior. Is this known? by design? or how do I resolve this issue?

 

Seth Atkins