Doctrine: Convert a createQuery query to createQueryBuilder

Question

This is the current query that I have, and it works fine:

$q = $this->_em->createQuery("SELECT s FROM app\models\Sub s 
                              LEFT JOIN s.case c
                              WHERE s.type LIKE '%$type'
                              AND c.id = '$id'");


foreach ($q->getResult() as $row) {
   $results[$row->getId()] = $row;
}

I want to convert this to the QueryBuilder API structure, this is what I have so far, but it's not working correctly. (And I need the results as the same format as above).

 $q = $this->_em->createQueryBuilder();

 $q->add('select', 's')
   ->add('from', 'app\models\Sub s')
   ->add('leftJoin', 's.case c')
   ->add('where', 's.type LIKE :type')
   ->add('where', 'c.id = :case');
   ->setParameter('type', "%$type");
   ->setParameter('case', $id);

And again, this isn't working properly, and I don't know how to get the results in the same format as above. Thanks!

Answer 1

Close, but no cigar. You'll need to learn how to use the Expr classes that comes with Doctrine in order to create the correct WHERE clause.

http://www.doctrine-project.org/docs/orm/2.1/en/reference/query-builder.html#expr-classes

In the example you provided, you are actually aiming for something like this (not tested):

$q->add('select', 's')
  ->add('from', 'app\model\Sub s')
  ->add('leftJoin', 's.case c')
  ->add('where', $q->expr()->andX($q->expr()->like('s.type', ':type'), $q->expr()->eq('case', ':case')))
  ->setParameter('type', "%$type")
  ->setParameter('case', $id);

As your WHERE clause gets more complicated, so will the nesting of expressions.