OS : Ubuntu 9.04
PHP : PHP 5.2.6-3ubuntu4.5

I have found this in the google group, and he provides more detailed info, and appears to be the same exact issue : http://groups.google.com/group/doctrine-user/msg/8e4a8a673428fff0

seems to be another similar issue here : http://www.doctrine-project.org/jira/browse/DC-328

I'm having that problem and I taked a look into the code, found this:

I have a query that looks like this:
$q = Doctrine_Query::create()
->select('af.id as id, af.user_id as user')
->from('ActivityFeed af')
->innerJoin('af.user as afu')
->orderBy('af.time DESC');

The sql query without aliases in SELECT or without join is built that way:
SELECT a.id AS a_id, a.user_id AS a_user_id ... -> This is returning all the records

The sql query with aliases in SELECT is built that way:
SELECT a.id AS a_0, a.user_id AS a_1 FROM ... -> This is returning only one record

In Doctrine_Hydrator_Graph::_gatherRowData, line 288 there's the following call:

$fieldName = $table->getFieldName($last);
Where $last is the last part of the column alias, for example "0" in "a__0". This way the call asks the table for the name of the "0" field and the table returns "0" so I think we're not getting the right field name (it must be "id").

I'm not understanding the whole hydration process but replacing

$fieldName = $table->getFieldName($last);

with:

if(isset($this->_queryComponents[ $cache[$key]['dqlAlias']]['agg'])) {
$fieldName = $table->getFieldName($this->_queryComponents[ $cache[$key]['dqlAlias']]['agg'][$last]);
} else {
$fieldName = $table->getFieldName($last);
}

solves the problem almost in my case.

Hope it will help to solve the issue.

It seems the suggested change causes many failures in our test suite. Can you give it a try and confirm?

Just commenting to say that I can confirm this bug exists. It's happening for me too.
I will try and dig deeper when I have some time.

As the error is located in Doctrine_Hydrator_Graph, HYDRATE_RECORD has the same behaviour.

I'm trying to find a solution.

This only occurs if your alias your identifier field.
Doctrine needs to know which field is the identifier to hydrate records.
The identifier aliases is formed to x__0, and then, as other commented has found.
Doctrine then has no way of determining which field is the identifier.

Hi everybody,

we really need a unit test here. The provided patch breaks all aliased queries in our application. I will provide a patch and it hopefully solves your problem Juan. If not, please provide more information or add another test-method to the provided unit test.

I had to rewrite some of the unit tests cause the order of the fields in the generated sql statement has changed due the provided patch.

Enrico

http://github.com/doctrine/doctrine1/commit/e3ae69c2260dae6dfbceb4e24138b2379f3da2e6#commitcomment-169495
http://github.com/estahn/doctrine1/tree/DC-585

Hi,

I have a little issue with this bug report...

I'm currently using Doctrine 1.2 at revision 7691, and I've encounter a bug when select the primary key of a model with a custom alias (typically $query->addSelect( 'r.id as my_id' )), the issue was that Doctrine automatically add `r`.```id``` AS `r_1` to the select clause, in addition to the correct `r`.`id` AS `r_1` part.

So, I've browse the code to finally found where does this strange thing comes from, and I've found it on Doctrine_Query::parseSelect() method, just at the line 663:

// Fix for http://www.doctrine-project.org/jira/browse/DC-585
// Add selected columns to pending fields
if (preg_match('/^([^\(]+)\.(\'?)(.*?)(\'?)$/', $expression, $field)) {
$this->_pendingFields[$componentAlias][$alias] = $field[3];
}

So I'm wonder : where does this patch is related to this bug report?

Whatever, the bug I've encounter is very simple : the regular expression that extract the field name takes care about ' but not ´.

You will find a patch for the bug DC-585-b in a few minutes... This patch just make the regular expression taking account of ' and ´ and also remove all useless parentheses in expression (by this way PCRE get less matches to capture and we can earn some precious microseconds).

Also, this patch should be completed because the query still have two `r`.`id` AS `r_1` parts, that may cause some other issues with some databases...

IMPORTANT NOTE: The previous revision 7674 of Doctrine_Query does not cause the bug encounter with the few lines of code above, just because they were not there. They really must be review.

Loops

Damn, the bug above was corrected during a few weeks and comes back with the new branch of Doctrine 1.2.14.

Does anybody can re-apply the corrective patch (renamed DC-585-c), please ?

I repeat it agin, but these few lines of code really need to be review.

Loops

I also have this problem .. Anyone know howt o fix it?

$query->execute(array(), Doctrine::HYDRATE_ARRAY); // returns one row only
$query->execute(array(), Doctrine::HYDRATE_RECORD); // returns one row only

$query->execute(array(), Doctrine::HYDRATE_SCALAR); // returns result as expected

I found solution for my problem:

Table had ID field but it was not primary key. When I deleted this column, doctrine started to work as expected.

I have run into the same issue.

Using the default hydration, I get only one record.

Using "$query->execute(array(), Doctrine::HYDRATE_SCALAR);", I get all the expected records.