Doctrine won't let me select specific fields

Question

The symfony framework features an app/console file that can be executed via php to perform some maintenance tasks. It allows users to run DQL queries as well:

# php app/console doctrine:query:dql --hydrate=array \
    'SELECT u.id, u.nameFirst, u.nameLast  FROM DatabaseBundle:User u'  
array
  0 => 
    array
      'id' => string '1' (length=1)
      'nameFirst' => string 'jaroslav' (length=8)
      'nameLast' => string 'rakhmatoullin' (length=13)
  1 => 
    array
      'id' => string '2' (length=1)
      'nameFirst' => string 'Båb Kåre' (length=10)
      'nameLast' => string 'Ytrefoss' (length=8)

Observe that I selected three specific columns. The problem I'm having is that a similar query gives me an error when two tables are joined.

# php app/console doctrine:query:dql  --hydrate=array \
    'SELECT u.id , r FROM DatabaseBundle:User u JOIN u.roles r'

  [Doctrine\ORM\Query\QueryException]                   
  [Semantical Error] line 0, col -1 near 'SELECT u.id ,': 
     Error: Cannot select entity through identification variables 
     without choosing at least one root entity alias.  

The following returns the whole user joined with his roles:

# php app/console doctrine:query:dql  --hydrate=array \
     'SELECT u, r FROM DatabaseBundle:User u JOIN u.roles r'

Obviously, I'm missing something.

Any ideas? I would appreciate links to appropriate docs too (on this specific matter).

Wattenberg

I don't understand the error message very well, but I recall experiencing similar error messages, and solving my problem by selecting r.id, so I think you should try selecting more fields. – Katalin

what about this? 'SELECT u.id , r.* FROM DatabaseBundle:User u JOIN u.roles r' – Rising

greg0ire that's correct. i think you can't select a single field AND an "entity" object in a query like that. – Rising

@Rising r.* seems to be equivalent to r (the ORM selects all fields anyway and db never sees 'r.*' -- according to docs, somewhere). The problem was resolved by explicitly selecting fields from the r alias as well: SELECT u.id , u.username , r.id, r.title FROM DatabaseBundle:User u JOIN u.roles r – Lindemann

Answer 1

From the documentation on "Partial Object Syntax":

By default when you run a DQL query in Doctrine and select only a subset of the fields for a given entity, you do not receive objects back. Instead, you receive only arrays as a flat rectangular result set, similar to how you would if you were just using SQL directly and joining some data.

If you want to select partial objects you can use the partial DQL keyword.

php console doctrine:query:dql --hydrate array \
  'SELECT partial s.{name ,id}, partial c.{name, id } 
   FROM DatabaseBundle:ProductCategories c 
   JOIN c.suppliers s ORDER BY s.name, c.name'

Wattenberg

Note, you must include the primary key in a partial query - otherwise you get a nasty error. – Refugee

Two years later I don't quite understand my own answer, but if I remember correctly, the key is that: every product category is mapped as "c" and every category.supplier[] is mapped as s. Every category has suppliers mapped through an "@annotated-join" in the ProductCategories class. Thanks for all the up-votes. I think 10 is enough. – Lindemann

But what if you really want to have the full object back? I mean for instance some fields of objects A and then the whole object B? – Apostolate

then you only add the table's alias without a dot in the select line :) – Tamatamable