How to convert SQL query with subquery to Doctrine QueryBuilder format?

user_12210886 0
Viewed 88

I want to transcribe this sql statement to doctrine format. It was complicated because I don't know how to perform subqueries. The sentence is as follows:

SELECT * FROM articulo WHERE descripcion = (SELECT descripcion FROM articulo WHERE codigo LIKE 4060981104537)

doctrine-orm doctrine mysql php
3 Answers

You can use Doctrine expression API for subqueries. Example for your query:

you should put following code to Doctrine repository of your articulo entity

$codigo = '4060981104537';
$qb = $this->createQueryBuilder('a');

$qb->where(
  $qb->expr()->in(
    'a.descripcion',
     $this->createQueryBuilder('a2')
       ->select('a2.descripcion')
       ->where('a2.codigo LIKE :codigo')
       ->setParameter('codigo', $codigo)
       ->getDQL()
     )
);
user_131973460

Do sth like this:

$codigo = 4060981104537;
$expr = $em->getExpressionBuilder();
$em->createQueryBuilder()
   ->select('*')
   ->from('articulo')
   ->where(
       $expr->in(
           'articulo.descripcion',
           $em->createQueryBuilder()
               ->select('*')
               ->from('articulo', 'secondArticulo')
               ->where('secondArticulo.codigo LIKE :codigo')
               )
               ->getDQL()
       )
   )
   ->setParameter('codigo', $codigo);

And for more info look at this link

user_130609810

I solved it as follows:

$codigo = '4060981104537';
$qb = $this->createQueryBuilder('a2')
           ->select('a2.descripcion')
           ->where('a2.codigo LIKE :codigo')
           ->setParameter('codigo', $codigo);

$descripcion =  $qb->getQuery()->getResult();   
   
$qb = $this->createQueryBuilder('m')
           ->where('m.descripcion= :descripcion')
           ->setParameter('descripcion', $descripcion);

return $qb->getQuery()->getResult();
user_122108860
Related