Symfony2 - Query Builder LIKE null values

Question

I am trying to create a dynamic query in response to a user search from users. I have a problem: when I create a query, I have no results, because comparing SELECT LIKE columns does not work with NULL values. How can I solve this problem, given that the request is dynamically generated? This way, users can give values ​​or not search for criteria ...

This is my code:

$qb->add('select', 'f') ->add('from', 'Bundle:Object f') ->add('where', $qb->expr()->andx( $qb->expr()->like('f.c1',':c1'), $qb->expr()->like('f.c2',':c2'), $qb->expr()->like('f.c3',':c3'))) ->add('orderBy', 'f.nnumCatalogo ASC'); if ($data->getField1() != null) { $isField1 = true; } if ($data->getField2() != null) { $isField2 = true; } if ($data->getField3() != null) { $isField3 = true; } if ($isField1) { $qb->setParameter('c1', $data->getField1()); } else { $qb->setParameter('c1', '%'); } if ($isField2) { $qb->setParameter('c2', $data->getField2()); } else { $qb->setParameter('c2', '%'); } if ($isField3) { $qb->setParameter('c3', $data->getField3()); } else { $qb->setParameter('c3', '%'); } 

With this code, I have no results due to NULL values ​​in some columns not selected with LIKE '%' (mysql).

Hello,

Javier

Answer 1

Try the following:

 $qb->add('select', 'f')->add('from', 'Bundle:Object f'); if ($data->getField1() != null) { $qb->andWhere('f.c1 like :c1') ->setParameter('c1', $data->getField1()); } if ($data->getField2() != null) { $qb->andWhere('f.c2 like :c2') ->setParameter('c2', $data->getField2()); } if ($data->getField3() != null) { $qb->andWhere('f.c3 like :c3') ->setParameter('c3', $data->getField3()); } $qb->add('orderBy', 'f.nnumCatalogo ASC');