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:

Description

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).


// $select instanceof Zend_Db_Select:

        $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


        $select
            ->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. :)

Comments

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


$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'));
$adapter->setIdentity($person_tag)
              ->setCredential($person_password);
$result = $auth->authenticate($adapter);

what happens in the select is this:


            [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:


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:


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