doctrine2 - Doctrine 2: Subquery alias creates an extra level in results -

consider following dql:

select   a,   (select count(c.id) comment c c.article_id = a.id) num_comments article 

i expect result contain article columns plus num_comments on same level, instead article columns wrapped in array @ index 0:

array(   0 => array(     0 => array(       "id" => 1,       "title" => "title",     ),     "num_comments" => 15   ),   1 => array(     0 => array(       "id" => 2,       "title" => "title",     ),     "num_comments" => 20   ) ); 

how keep article columns , fields on same level?

i'll split answer in short , long explanation (and alternate answers).

short explanation:

select a.field1      , ...      , a.fieldn      , (select count(c.id) comment c c.article_id = a.id) num_comments    article 

then hydrate array , return you're expecting.

long explanation:

whenever refer entity (in case, "select a"), means requesting return entire entity isolated other element. doctrine default assign entity index 0 , goes on depending on how many entities you're selecting. change behavior, have alias entity, did sub-query:

select article      , (select count(c.id) comment c c.article_id = a.id) num_comments    article 

this return like:

array(   0 => array(     "article" => array(       "id" => 1,       "title" => "title",     ),     "num_comments" => 15   ),   1 => array(     "article" => array(       "id" => 2,       "title" => "title",     ),     "num_comments" => 20   ) ); 

if looking not return entire entity, can use partial support , return fields you're interested in. let's support want id (mandatory, because it's entity identifier) , title:

select partial a.{id, title} article      , (select count(c.id) comment c c.article_id = a.id) num_comments    article 

another approach encapsulating result dto. provide more oo control when decide hydrate values object. dql this:

select new articledto(a, count(c.id))    article   left join a.comments c  group a.id 

the reason why cannot place entire sub-query argument due limitation on dql. none ever requested full sub-query, follows jpa suggests scalarexpression.

cheers,

guilherme blanco