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.

Jules R · Accepted Answer · 2018-12-17 12:44:07Z

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, 2018 at 12:44

Jules R

5382 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vimuth Over a year ago

is there a way to use like in this situation?

Jules R Over a year ago

Didn't try LIKE with DC2 field, but maybe. However, as I said, you should not do it.

vimuth Over a year ago

I found an answer with regexp. But thanks so so much for your explanation and reply

vimuth · Accepted Answer · 2018-12-17 19:41:53Z

1

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?

edited Dec 17, 2018 at 19:41

answered Dec 17, 2018 at 18:51

vimuth

5,87355 gold badges98 silver badges128 bronze badges

Collectives™ on Stack Overflow

Query an array type with php array using doctrine

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related