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

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

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

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

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

my previous comment was in reference to the file i attached

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

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

Fixed in r18511 in release 1.9 branch