Query an array type with php array using doctrine

Question

I need to query an mysql array type with php array by using doctrine.

This my db column is dc2 type array and contains values like this.

a:1:{i:0;s:3:"4wd";}

And this is my querybuilder query,

$qb = $this->createQueryBuilder('c'); 
$qb->andWhere('c.equipment IN (:equipment)')
   ->setParameter('equipment', array('4wd'));
$qb->getQuery()->getResult();

But unfortunately this query returns only null output. It would be great if someone can help solve this.

score 1 · Answer 1 · answered Dec 17 '18 at 12:44

1

To be clear about DC2 type, it does NOT exist in SQL. DC2 is a comment added by doctrine to specify that it is a text value containing an array.

So, you should select the column and fetch it first. Doctrine is gonna process it and give you an array afterward, so you can do whatever you wanna do with it.

You could possibly search in the text field with LIKE and filter, but this is bad practice, make an Equipment entity and add a ManyToMany relation between Equipment and your entity. Where clause with a ManyToMany relation well explained here.

answered Dec 17 '18 at 12:44

Jules R

553
2
18

is there a way to use like in this situation? – vimuth Dec 17 '18 at 12:56
Didn't try LIKE with DC2 field, but maybe. However, as I said, you should not do it. – Jules R Dec 17 '18 at 12:59
I found an answer with regexp. But thanks so so much for your explanation and reply – vimuth Dec 17 '18 at 18:52

vimuth · Accepted Answer · 2018-12-17T19:41:53.013

Finally I found an answer with REGEXP, But for symfony 4 you need DoctrineExtensionsBundle

"beberlei/DoctrineExtensions": "0.3.x-dev",

in doctrine.yaml

doctrine:
    orm:
        dql:
            string_functions:
                regexp: DoctrineExtensions\Query\Mysql\Regexp

in repocitery

$equipment = implode("|", array('4wd', 'AbD'));
$qb->andWhere('REGEXP(c.equipment, :regexp) = true')
                    ->setParameter('regexp', $equipment);

These two threads were really helpful for the answer and background. MySQL LIKE IN()? And Regex with Doctrine 2 query builder?

Query an array type with php array using doctrine

2 Answers2