0

I've got the following query:

<?php $em = $this->getDoctrine()->getManager(); $data['ps'] = $em->createQuery('SELECT s,ps FROM AppBundle:Sucursales s LEFT JOIN AppBundle:ComprasProductosSucursales ps WITH s.id = ps.sucursal')->getResult();

But I get a result like in the following image:

![Example of Doctrine2 resultset][1]

I expected that $data['ps'][1] to be part of $data['ps'][0], otherwise the iteration of the array is going to be very weird.

So, it's possible to get the result of the joined table AppBundle:ComprasProductosSucursales as columns of the AppBundle:Sucursales data?

ORMs:

AppBundle\Entity\Sucursales: type: entity table: sucursales # Note: no relations defined, deleting the rest of the columns for clarity AppBundle\Entity\ComprasProductosSucursales: type: entity table: compras_productos_sucursales manyToOne: producto: targetEntity: ComprasProductos sucursal: targetEntity: Sucursales cascade: { }
Improve this question
4
  • Does your posted query really match your actual query because I don't see how it can be working at all. Commented Mar 15, 2015 at 15:38
  • Actually yes, it does produces de following SQL query: SELECT s0_.id AS id0, s0_.nombre AS nombre1, s0_.telefono AS telefono2, s0_.direccion AS direccion3, s0_.activo AS activo4, c1_.id AS id5, c1_.cantidad_promedio AS cantidad_promedio6, c1_.producto_id AS producto_id7, c1_.sucursal_id AS sucursal_id8 FROM sucursales s0_ LEFT JOIN compras_productos_sucursales c1_ ON (s0_.id = c1_.sucursal_id) Commented Mar 15, 2015 at 15:57
  • And when I run that query in Workbench I get the desired results, 1 row for "Sucursal" data along with extra columns for "ComprasProductosSucursal" data if any, otherwise null Commented Mar 15, 2015 at 15:58
  • When using getScalarResult I get the exact result I'm looking for (example) but fields are prefixed by table alias, this can be ok though. Commented Mar 15, 2015 at 16:04

1 Answer 1

Reset to default
1

Assuming you have your entities related then normally your DQL query would be something like:

 $dql <<<EOT SELECT s,ps FROM AppBundle:Sucursales s LEFT JOIN s.ps ps EOT;

Doctrine takes care of the with condition on the join and will give you a nested set of results. The assumes that your Sucursales has a collection property called ps. I guess I am also assuming that you are creating Doctrine 2 entities and have them related.

More info: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#joins

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you, actually the relation is in ComprasProductosSucursal. I'll update the question with the relations. Also I don't have a collection named ps, it's the alias I've set for the entity ComprasProductosSucursal
Just change s.ps to s.whateverYouCalled it.
I've updated the question with the ORMs, I tried your change: SELECT s FROM AppBundle:Sucursales s LEFT JOIN s.ComprasProductosSucursales ps but I get: [Semantical Error] line 0, col 76 near 'ps': Error: Class AppBundle\Entity\Sucursales has no association named ComprasProductosSucursales
From your mapping it looks like you have a relation from ComprasProductosSucursales to Sucursales but not the other way around. So doctrine will not understand the query. You need to add the relation or select from ComprasProductosSucursales and join Sucursales.
I though about the second approach but I need this query to get all values from Sucursales and if there's corresponding values in ComprasProductosSucursales then get them, if not then show them values as null.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.