How to use a query builder object inside an IN clause of another query builder object

Question

I need to create a query builder expression that use an IN statement where I want to put another query create by querybuilder too. In DQL, the query that i can get is:

SELECT u
FROM BundleUsuarioBundle:Usuario u
WHERE u.id IN (
    SELECT u2.id
    FROM BundleTramiteBundle:ExamenTeorico et
    JOIN et.usuarioHabilitacion u2
    WHERE u2.centro = :centro
) ORDER BY u.apellido ASC, u.nombre ASC

but i dont use that because I need put that in 'query_builder' option of an entity field of a form. So, i need a QB but I dont know how I can nest querybuilder expressions. I actually have the two queries in QueryBuilder format but separated:

$qb = $this->em->createQueryBuilder();

$qb
    ->select('usuario_habilitacion.id')
    ->from('BundleTramiteBundle:ExamenTeorico', 'examen_teorico')
    ->join('examen_teorico.usuarioHabilitacion', 'usuario_habilitacion');

$user = $this->getUser();
if ( !$this->esTipoASPV($user) ) {
    $centro = $this->getCentro();

    $qb
        ->andWhere($qb->expr()->eq('usuario_habilitacion.centro', ':centro'))
        ->setParameter(':centro', $centro->getId());
}

and:

    $er
        ->createQueryBuilder('u')
        ->where($qb->expr()->in('u.id', ___???___ ))
        ->addOrderBy("u.apellido", "ASC")
        ->addOrderBy("u.nombre", "ASC");

walkietalkie walkietalkie · Accepted Answer · 2015-06-18T18:20:18

You can get the result of your sub-query inside the first query but calling getDQL() and placing it inside a where clause in your first query. Make sure you select the id in the sub-query.

Example:

$qb->andWhere($qb->expr()->in('u.id', $er->getDQL()))

How to use a query builder object inside an IN clause of another query builder object

1 Answers