QueryBuilder : how to compose subselect clause

Question

I'm having trouble converting a plain SQL clause to a Doctrine query. I've added the clause in plain SQL below:

$query = $doc->getEntityManager ()
 ->createQueryBuilder ()
 ->select ( 'r')
 ->from ( 'AppBundle:CFormResponse', 'r' )
 ->where ( 'r.formId = :id AND NOT (select f.private from cform f where f.FORM_ID = :id) ' )
 ->setParameter ( 'id', $formId )->getQuery ();
$result = $query->getResult();

My attempt to convert this clause into DQL is as follows (so far):

$qb = $doc->getEntityManager ()->createQueryBuilder ();
$query = $qb
    ->select ( 'r')
    ->from ( 'AppBundle:CFormResponse', 'r' )
    ->where ( $qb->expr()->andX(
            $qb->expr()->eq('r.formId', ':id'),
            $qb->expr()->addSelect('(SELECT f.private
                FROM AppBundle:CForm f
                WHERE f.formId = :id)'
            )
            ) )
    ->setParameter ( 'id', $formId )->getQuery ();
$result = $query->getResult();

with the addSelect indicating where I'm stuck. Can anyone point me in the direction of how to do this?

score 0 · Answer 1 · answered May 23 '17 at 08:32

Basically, you need to add not to your addSelect:

$query = $qb
    ->select ( 'r')
    ->from ( 'AppBundle:CFormResponse', 'r' )
    ->where ( $qb->expr()->andX(
            $qb->expr()->eq('r.formId', ':id'),
            $qb->expr()->not(
                $qb->expr()
                ->select(`f.private`)
                ->from('AppBundle:CForm', 'f')
                ->where('f.formId = :id'))
            )
            ) )
    ->setParameter ( 'id', $formId )->getQuery ();

QueryBuilder : how to compose subselect clause

1 Answers1