Add a new field from an object in a DQL query

Question

Actually I'm using something like that in DQL (Doctrine) in a Symfony2 / twig / Doctrine project:

$query = $em->createQuery('
                SELECT e, l
                FROM FrontendBundle:Entrega e
                JOIN e.libro l
                WHERE EXISTS (SELECT r1.id FROM FrontendBundle:Reserva r1
                                WHERE r1.entrega = e.id)'
                );

Look at I'm filtering the entrega's that appear in some Reserva.entrega field. There is an OPTIONAL OneToOne relation in Entrega.

The SELECT e, l it's useful for me, because I use code like that in twig: {{ entrega.libro.id }} ...

The question is if exists another way to select ALL the Entregas, but having a new field in each one of them that contains the associated Reserva object, or null.

Something like:

$query = $em->createQuery('
                SELECT e, l        
             **(SELECT r1.id FROM FrontendBundle:Reserva r1
                        WHERE r1.entrega = e.id) as myReserva**

                FROM FrontendBundle:Entrega e
                JOIN e.libro l;

This solution doesn't work for me because the extra-select makes that the result becomes a rectangular Array, no and Array of Entrega Objects. I would like:

{{  entrega.myEntrega }}

after the consult.

I know that this situation needs a ManyToMany relation. But my project is next to finish, and this is a new function. I want to reuse all my twig views and logic with a few changes.

Mark Mark · Accepted Answer · 2015-07-29T13:12:55

The simplest way you can achieve this is to simply join your Reserva entity to the select.

For this you will have to map an association in your Entrega entity, called for example: reserva.

Then you can simply run the following query:

$query = $em->createQuery('
            SELECT e, l, r
            FROM FrontendBundle:Entrega e
            JOIN e.libro l
            LEFT JOIN e.reserva r'
            );

Only Reserva entities for which a relation exists in Entrega will be added to the result set.

Add a new field from an object in a DQL query

1 Answers