Real dynamic DQL with doctrine in symfony 3.4

Question

I'm trying to achieve a dynamic query with DQL in doctrine. I've checked several post about this subject but all the solutions are static. I want to achieve somethin like this:

$qb->where(
            $qb->expr()->orX(
                $qb->expr()->like('e.cliente', ':cliente_tag'),
                $qb->expr()->like('e.cliente', ':cliente_tag2'),
                $qb->expr()->like('e.cliente', ':cliente_tag3')
            ),
            $qb->expr()->orX(
                $qb->expr()->like('e.apoderado', ':apoderado_tag'),
                $qb->expr()->like('e.apoderado', ':apoderado_tag2'),
                $qb->expr()->like('e.apoderado', ':apoderado_tag3')
            )
        );

but inside a loop like this:

foreach ($options['camposTexto'] as $i => $campoTexto) {
             switch ($campoTexto['appliedTo']) {
                 case 'apoderado': {
                     $exp = [];
                     foreach ($campoTexto['tags'] as $tag) {
                         $exp[] = $qb->expr()->like('e.apoderado', ':apoderado_tag' . $i);
                         $parameters['apoderado_tag' . $i] = '%' . $tag . '%';
                     }

                     if ($isFirst) {
                         $isFirst = false;
                         $qb->where($qb->expr()->orX($exp));
                     } else {
                         $qb->andWhere($qb->expr()->orX($exp));
                     }

                     break;
                 }
                 case 'cliente': {
                     $exp = [];
                     foreach ($campoTexto['tags'] as $tag) {
                         $expresiones[] = $qb->expr()->like('e.cliente', ':cliente_tag' . $i);
                         $parameters['cliente_tag' . $i] = '%' . $tag . '%';
                     }

                     if ($isFirst) {
                         $isFirst = false;
                         $qb->where($qb->expr()->orX($exp));
                     } else {
                         $qb->andWhere($qb->expr()->orX($exp));
                     }

                     break;
                 }
             }
         }

tags is an array of strings. As you see I passed the array of expresions but doctrine throws me an Exception.

So far so now I have not found any solution to my problem.

Any Idea?

Thanks in advance!

You should provide the exception you get. Also I think your case looks like a use case for "in" instead of "orX"? — Tobias Xy, Apr 09 '18 at 22:47
This does not answer your question, but you can simplify your code by skipping all the "if($isFirst)" stuff by using "andWhere" for all statements; no need to use "where" for the first. Doctrine will form the correct statement for you. — ehymel, Apr 09 '18 at 23:40
The exception is that I'm handling the Base class for DQL as an array or Expected Base got array exception when I pass the array expecting doctrine iterates over and pull out all the previous expressions. @TobiasXy I need to check the fields are **like** some value not **in** some array, Eg: water would be **like** asd**water**loo but won't be **in** [waterloo,africa] — Juan I. Morales Pestana, Apr 10 '18 at 12:14
@ehymel is just that I wanted to try something different. thanks anyway both of you — Juan I. Morales Pestana, Apr 10 '18 at 12:15

score 0 · Accepted Answer · answered Apr 10 '18 at 16:07

Looking at this post I figured out the solution. It would be something like this:

    case 'apoderado': {
        $orX = $qb->expr()->orX();
        foreach ($campoTexto['tags'] as $y => $tag) {
        $orX->add($qb->expr()->like('e.apoderado', $qb->expr()->literal('%' . $tag . '%'))); //<= with literal because I can't set the parameters later in the qb
   }
  $expresiones[] = $orX;
  break;
  }

after all the case/break

  $andX = $qb->expr()->andX();      
  $qb->where($andX->addMultiple($expresiones));   
  return $qb->getQuery()->getResult();

Real dynamic DQL with doctrine in symfony 3.4

1 Answers1