ZF-6653: Calling from() and join() "out of order" causes incorrect SELECT generation

Issue Type: Bug Created: 2009-05-14T08:12:02.000+0000 Last Updated: 2009-10-12T07:36:56.000+0000 Status: Resolved Fix version(s): - 1.9.4 (13/Oct/09)

Reporter: Dalibor Karlovic (dkarlovi) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: Attachments: - Select.php.diff


I was trying to adjust the Zend_Auth DB adapter result to include the user role for a simple ACL I'm doing (I've tables "users" and "roles", with the former containing a FK to the latter).

<pre class="highlight">
// $select instanceof Zend_Db_Select:

            ->from(array('u' => 'users'), array('username'))
            ->joinLeft(array('r' => 'roles'), 'u.role_id = r.role_id', array('role' => 'name'));

// gives SELECT `u`.`username`, `r`.`name` AS `role` FROM `users` AS `u` LEFT JOIN `roles` AS `r` ON u.role_id = r.role_id

            ->joinLeft(array('r' => 'roles'), 'u.role_id = r.role_id', array('role' => 'name'))
            ->from(array('u' => 'users'), array('username'));

// gives SELECT `r`.`name` AS `role`, `u`.`username` FROM `roles` AS `r` INNER JOIN `users` AS `u`

The DB auth adapter returns an empty $select so anything I do to it get's done first. This may be viewed as the adapter's fault but IMHO, you shouldn't force a specific order of calling the methods: I've said from('table') and expect to really be FROM table. :)


Posted by Dalibor Karlovic (dkarlovi) on 2009-05-14T08:12:50.000+0000

Yeah, the code tag didn't take, hope this doesn't mean my issue get's disqualified. :)

Posted by Shaddy Zeineddine (shaddyz) on 2009-06-19T10:39:50.000+0000

I can confirm this. I was trying to customize Zend_Auth because i store the password and identity in different tables

<pre class="highlight">
$adapter = new Zend_Auth_Adapter_DbTable($db, array('p'  => 'people'), 'p.person_tag', 'pd.person_password');
$select = $adapter->getDbSelect();
$select->join(array('pd' => 'people_detail'), 'p.person_id = pd.person_id', array('pd.person_password'));
$result = $auth->authenticate($adapter);

what happens in the select is this:

<pre class="highlight">
            [from] => Array
                    [pd] => Array
                            [joinType] => inner join
                            [schema] => 
                            [tableName] => people_detail
                            [joinCondition] => p.person_id = pd.person_id

                    [p] => Array
                            [joinType] => inner join
                            [schema] => 
                            [tableName] => people
                            [joinCondition] => 


and that creates a query like this:

<pre class="highlight">
SELECT `pd`.`person_password`, `p`.*, (CASE WHEN `pd`.`person_password` = 'password' THEN 1 ELSE 0 END) AS `zend_auth_credential_match` FROM `people_detail` AS `pd` INNER JOIN `people` AS `p` WHERE (`p`.`person_tag` = 'test')

but it should be like this:

<pre class="highlight">
SELECT `pd`.`person_password`, `p`.*, (CASE WHEN `pd`.`person_password` = 'password' THEN 1 ELSE 0 END) AS `zend_auth_credential_match` FROM `people` AS `p` INNER JOIN `people_detail` AS `pd` ON p.person_id = pd.person_id WHERE (`p`.`person_tag` = 'test')

I'm guessing it should check the join condition to know if it should be shown as a join

Posted by Shaddy Zeineddine (shaddyz) on 2009-06-22T19:05:44.000+0000

A solution to the problem which differentiates tables added with the from method

Posted by Shaddy Zeineddine (shaddyz) on 2009-06-22T19:06:21.000+0000

my previous comment was in reference to the file i attached

Posted by Nikolay Goldschmidt (ccppprogrammer) on 2009-09-03T02:44:03.000+0000

I have same problem. When use join/joinLeft/joinRight Zend_Auth_Adapter_DbTable generate sql statement without second argument in join function (joinCondition).

Posted by Ralph Schindler (ralph) on 2009-10-03T15:13:33.000+0000

Fix in trunk at r18475, might go into 1.9, needs to be decided.

Posted by Ralph Schindler (ralph) on 2009-10-12T07:36:48.000+0000

Fixed in r18511 in release 1.9 branch

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.