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).

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. — greg0ire, Feb 27 '12 at 13:17
what about this? `'SELECT u.id , r.* FROM DatabaseBundle:User u JOIN u.roles r'` — jere, Feb 27 '12 at 13:23
greg0ire that's correct. i think you can't select a single field AND an "entity" object in a query like that. — jere, Feb 27 '12 at 13:25
@jere 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 — Ярослав Рахматуллин, Feb 27 '12 at 17:48

score 36 · Accepted Answer · edited Jan 10 '21 at 15:11

36

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'

edited Jan 10 '21 at 15:11

greybeard

2,249
8
30
66

answered Feb 29 '12 at 19:16

Ярослав Рахматуллин

7,599
1
41
60

4

Note, you must include the primary key in a partial query - otherwise you get a nasty error. – Caspar Harmer Oct 22 '13 at 03:16
2

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. – Ярослав Рахматуллин Jun 20 '14 at 19:59
1

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? – Sergio Negri Jan 15 '15 at 10:48
then you only add the table's alias without a dot in the select line :) – Edwin O. Sep 06 '17 at 15:46

Doctrine won't let me select specific fields

1 Answers1

Linked