Doctrine subqueries in SELECT

Question

SELECT u FROM AcmeBundle:Users u

The statement above returns all records from the users table. What if I want to add something else, like:

SELECT u, ( SELECT s.name FROM AcmeBundle:Shop s ) AS shop_name FROM AcmeBundle:Users u

Executing the second query throws an error that userId for array with keys "u, shop_name" does not exist. What's wrong?

That's not a valid Doctrine query. It would be helpful, if you'd tell what you are trying to achieve. — RoToRa
– RoToRa, Commented Jul 19, 2014 at 17:06
That's valid DQL query in symfony 2, from what I know. I'm simply trying to redurn everything from the users table, and one column from the shop table - just like in the second query. — khernik
– khernik, Commented Jul 19, 2014 at 17:14
Normally it's doesn't make sense to just get the data from one field. Doctrine is primary designed to return a collection of entities. You can return scalar values, but not a mixture of scalars and entities. In any case you'll need to join the two entities. What is the relation between the two tables? — RoToRa
– RoToRa, Commented Jul 19, 2014 at 17:25
One to many. I actually need this to paginate results (knplabs bundle). It only works when I pass the whole objects to the query. — khernik
– khernik, Commented Jul 19, 2014 at 17:41

RoToRa · Accepted Answer · 2014-07-19 17:47:40Z

Then you probably want something like

SELECT u, s FROM AcmeBundle:Users u JOIN u.shop s

where shop is the name of the property in the User entity that refers to the Shop entity.

You then can access the name of the shop when you have a User entity with

$user->getShop()->getName()

1 Answer 1