Dql in Query Builder

Question

I have been working some days with a dql problem.

In my model, y have 2 entities (machine and instance). Is a one-to-many relation, so a machina could have many instances, and an Instance only can have 1 machine.

Now, I want to get all machines which has some Instances. In normal dql i get it work correctly like that:

$ids = array(...)
$dql = "select M from AppBundle:Machine M INNER JOIN AppBundle:Instancia I WITH M.id = I.machine WHERE I.software IN (:ids)";
$query = $em->createQuery($dql)->setParameter('ids', $ids);
$maquinas = $query->getResult();

But when I want to translate in a QueryBuilder i'm not getting it to work. I'm triying some ways:

//First way
$qb = $this->createQueryBuilder("M")
->innerJoin('Instancia', 'I', 'WITH', 'M.id = I.machine')
->where('I.software IN (:ids)')
->setParameters('ids', $ids);       
$maquinas = $qb->getQuery()->getResult();

//Second way
$qb = $this->createQueryBuilder("x")
->select('M')
->from('AppBundle:Machine', 'M')
->innerJoin('Instancia', 'I', 'WITH', 'M.id = I.machine')
->where('I.software IN (:ids)')
->setParameters('ids', $ids);       
$maquinas = $qb->getQuery()->getResult();

What is my fault?

Which way is more faster, simple dql or a querybuilder??

Thank a lot!!

I get this error: Fatal error: __clone method called on non-object — Angel, May 08 '17 at 10:41
It could be any possible to add a where clause out of the dql statment? I mean for example: $dql = "select M from AppBundle:Maquina M INNER JOIN AppBundle:Instancia I WITH M.id = I.maquina"; $query = $em->createQuery($dql); $query->where("I.software IN (:ids)"); $query->setParameter('ids', $ids); — Angel, May 08 '17 at 10:42

score 0 · Answer 1 · answered May 08 '17 at 13:45

Use expression:

$qb = $this->createQueryBuilder("M")
->innerJoin('Instancia', 'I', 'WITH', 'M.id = I.machine')
->where($qb->expr()->in('I.software', $ids));  
$maquinas = $qb->getQuery()->getResult();

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class

Dql in Query Builder

1 Answers1