[DC-601] When using a join and giving an alias to each select column the hyrdrator only returns one row. Created: 25/Mar/10  Updated: 02/Aug/10

Status: Open
Project: Doctrine 1
Component/s: Query, Relations
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: will ferrer Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 2
Labels: None
Environment:

XP Xamp



 Description   

Hi All
I am running into a strange problem using Doctrine-1.2.2-- I have a multi row result set but only the first row of it is returned in the hyrdated array that is generated.
I have tracked this down in the code to get a basic idea of whats going on – it seems that the $id variable in the hydrateResultSet function in Doctrine_Hydrator_Graph isn't being populated properly with data about the fields that are returning from my query. The _gatherRowData function seems to never detect that one of my columns is an identifier ("if ($cache[$key]['isIdentifier'])

{" doesn't return true ever so the next line of code: "$id[$dqlAlias] .= '|' . $value;" doesn't run). I think this is the problem but I don't totally understand how the mapping process is taking place in this function so can't be absolutely sure. This hydration problem goes away if I don't use left joins in my query, or if I use a query where not every field I am requesting is assigned an alias (at least one of the fields doesn't use the 'as' syntax). For instance this php: {code}

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname as first_name');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Generates this DQL: 

SELECT Customer.firstname as first_name, Customer.postalcode as postalcode FROM Customer Customer LEFT JOIN Customer.Zip Zip

And this SQL:

SELECT c.firstname AS c_0, c.postalcode AS c_1 FROM customers c LEFT JOIN zips z ON c.postalcode = z.postalcode

Which results in this return after hyrdration: 

array('0'=>array('first_name'=>'Armando', 'postalcode'=>'00659'))

However the following code hydrates just fine: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->addSelect('Customer.firstname as first_name');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

As does this code: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Here is the yaml for the sample data I am testing on: 

detect_relations: false
package: Example
options:
type: INNODB
charset: utf8
Customer:
tableName: customers
columns:
customer_id:
type: integer(4)
primary: true
notnull: true
autoincrement: true
firstname:
type: string(45)
lastname:
type: string(45)
streetaddress:
type: string(45)
city:
type: string(45)
state:
type: string(45)
postalcode:
type: string(45)
relations:
Order:
type: many
local: customer_id
foreign: customer_id
Zip:
type: one
local: postalcode
foreign: postalcode
options:
type: InnoDB
Zip:
connection: default_schema
tableName: zips
columns:
postalcode:
type: varchar(30)
primary: true
latitude: 'float(10,6)'
longitude: 'float(10,6)'
city: string(50)
state: string(50)
country: string(50)
type: string(50)
relations:
Customer:
type: many
local: postalcode
foreign: postalcode


Perhaps there is something simple I am overlooking. To get around this I am just always selecting the primary key from my main table in every query.

Thanks in advance for any advice.
Will Ferrer



 Comments   
Comment by will ferrer [ 03/Apr/10 ]

This problem also exists in 1.2.2 so I have updated the post to reflect this.

Comment by Peter B├╝cker [ 06/Apr/10 ]

I experienced the same problem with Doctrine 1.2 (r7329). I also fixed this by adding the primary key of the table I select from to the select list.

Comment by will ferrer [ 07/Apr/10 ]

Hi Peter

Thats how I am currently working around the bug as well but hopefully it will get rectified in a later version of doctrine.

Thanks for the comment.

Will

Comment by Sam Doun [ 08/Jun/10 ]

Hi all,

I'm new to doctrine and since yesterday, I'm experiencing exactly the same behavior.
Any news about this subject ? Is there a doctrine version where the pb is solved ?
Help !

Regards,
Doun

Comment by will ferrer [ 08/Jun/10 ]

Hi Sam

Currently I am working around this bug by always adding the primary key of the table to the select (like peter also mentions above).

It is a work around for the problem but so far it has been reliable for me.

I hope that helps until there is a patch for it.

Will Ferrer

Comment by Sam Doun [ 09/Jun/10 ]

Hi Will

I'll do so.
Thank you VM.

Sam Doun

Comment by Shuchi Sethi [ 02/Aug/10 ]

Hi,

Has there been any patch release for the same?
We just upgraded from Doctrine 1.1 to 1.2 and our project now crashes.

For DQL -

function getServiceDetails($merchantId,$merchantServiceId) {
    $q = Doctrine_Query::create()
        ->select('b.*, m.name as merchant_service_name,payment_mode.name as payment_mode_name,payment_mode.id as paymentMode,p.id as paymentModeOption ')
        ->from('ServicePaymentModeOption b')
        ->leftJoin("b.MerchantService m")
        ->leftJoin("m.Merchant merchant")
        ->leftJoin("b.PaymentModeOption p")
        ->leftJoin("p.PaymentMode payment_mode")
        ->where("merchant.id=?",$merchantId)
        ->andWhere("b.merchant_service_id=?",$merchantServiceId)
        ->groupBy('p.name');

    return $res = $q->execute(array(),Doctrine::HYDRATE_ARRAY);
  }  

Result with Doctrine 1.1 is

Array
(
    [0] => Array
        (
            [id] => 1
            [merchant_service_id] => 1
            [payment_mode_option_id] => 1
            [created_at] =>
            [updated_at] =>
            [deleted] =>
            [created_by] =>
            [updated_by] =>
            [merchant_service_name] => NIS Passport
            [merchant_name] => NIS
            [payment_mode_option_name] => Bank
            [payment_mode_name] => Bank
            [paymentMode] => 1
            [paymentModeOption] => 1
            [MerchantService] => Array
                (
                    [merchant_service_name] => NIS Passport
                    [Merchant] => Array
                        (
                            [merchant_name] => NIS
                        )

                )

            [PaymentModeOption] => Array
                (
                    [payment_mode_option_name] => Bank
                    [paymentModeOption] => 1
                    [PaymentMode] => Array
                        (
                            [payment_mode_name] => Bank
                            [paymentMode] => 1
                        )

                )

        )
)

Result with Doctrine 1.2 is

Array
(
    [0] => Array
        (
            [id] => 1
            [merchant_service_id] => 1
            [payment_mode_option_id] => 1
            [created_at] =>
            [updated_at] =>
            [deleted_at] =>
            [created_by] =>
            [updated_by] =>
            [merchant_service_name] => NIS Passport
            [merchant_name] => NIS
            [payment_mode_option_name] => Bank
            [payment_mode_name] => Bank
            [paymentMode] => 1
            [paymentModeOption] => 1
        )
)

We have used Hydration for the result set at lot of places. Please suggest if there could be a fix without going about revising everything that has been coded.

Looking forward to a quick response.

Generated at Sat Oct 25 11:18:48 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.