[#ZF-6653] Calling from() and join() "out of order" causes incorrect SELECT generation - Zend Framework Issue Tracker

Details

Type: Bug
Status: Resolved
Priority: N/A
Resolution: Fixed
Affects Version/s: 1.8.1
Fix Version/s: 1.9.4
Component/s: Zend_Db_Select
Labels:
None

Tags:
- auth
- from
- join
- order
- select

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.

More Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Download All

Attachments

Select.php.diff

22/Jun/09 7:05 PM

1 kB

Shaddy Zeineddine

Options
- Show All
- Show Open

Sub-Tasks

Tests asssociated with ZF-6653 fails with Oracle

Resolved

Mickael Perraud

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Dalibor Karlovic added a comment - 14/May/09 8:12 AM

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

Show

Dalibor Karlovic added a comment - 14/May/09 8:12 AM Yeah, the code tag didn't take, hope this doesn't mean my issue get's disqualified.

Hide

Permalink

Shaddy Zeineddine added a comment - 19/Jun/09 10:39 AM

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

Show

Shaddy Zeineddine added a comment - 19/Jun/09 10:39 AM 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

Hide

Permalink

Shaddy Zeineddine added a comment - 22/Jun/09 7:05 PM

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

Show

Shaddy Zeineddine added a comment - 22/Jun/09 7:05 PM A solution to the problem which differentiates tables added with the from method

Hide

Permalink

Shaddy Zeineddine added a comment - 22/Jun/09 7:06 PM

my previous comment was in reference to the file i attached

Show

Shaddy Zeineddine added a comment - 22/Jun/09 7:06 PM my previous comment was in reference to the file i attached

Hide

Permalink

Nikolay Goldschmidt added a comment - 03/Sep/09 2:44 AM

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

Show

Nikolay Goldschmidt added a comment - 03/Sep/09 2:44 AM I have same problem. When use join/joinLeft/joinRight Zend_Auth_Adapter_DbTable generate sql statement without second argument in join function (joinCondition).

Hide

Permalink

Ralph Schindler added a comment - 03/Oct/09 3:13 PM

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

Show

Ralph Schindler added a comment - 03/Oct/09 3:13 PM Fix in trunk at r18475, might go into 1.9, needs to be decided.

Hide

Permalink

Ralph Schindler added a comment - 12/Oct/09 7:36 AM

Fixed in r18511 in release 1.9 branch

Show

Ralph Schindler added a comment - 12/Oct/09 7:36 AM Fixed in r18511 in release 1.9 branch

People

Assignee:

Ralph Schindler

Reporter:

Dalibor Karlovic

Vote (3)

Watch (3)

Dates

Created:

14/May/09 8:12 AM

Updated:

12/Oct/09 7:36 AM

Resolved:

12/Oct/09 7:36 AM