How to do a WHERE when the column is an array?

Question

I have and Entity : Company, which has a column: Type.

Type can either be a string, or an array of strings.

Ex: Type can be "Foo", "Bar", or array("Foo", "Bar").

I can't figure how to get all the Companies where the type contains "Bar".

I tried

$qb = $this->companyRepository()->createQueryBuilder("c");
            $companies = $qb
                ->select("c")       
                ->Where( $qb->expr()->in('c.type', array($qb->expr()->literal('Bar'))))
                ->getQuery()
                ->getResult();

Which only fetch the Companies where the type is "Bar", and not the ones where it is array("Foo", "Bar").

I tried $qb->expr()->like(...) instead of $qb->expr()->in(..) with the same results.

How can I get Companies where the type contains "Bar"? (Assuming type has more than just the 3 values I gave as an example)

Event though doctrine allows you to use an array-column-type, on the database level you can't access individual array elements (It's just a blob of text after all). As such you can't do queries on those. You have two options: regex queries, or normalize your database. — Yoshi, Sep 16 '16 at 13:39
Interesting. I can't normalize my database. How can regex help me ? I assume I will just need to fetch all the companies, and then test the type in a foreach loop.. — abernard, Sep 19 '16 at 06:51
The doctrine array column type requires you to not have commas inside the array values. So any value you could search for, either needs to be only value in column, needs to start with a comma or ends with a comma, or both. And for those four cases you'd need to write a valid regex and use it as your cirteria. — Yoshi, Sep 19 '16 at 06:56
Something like: `/(^|,)foo(,|$)/`, I'll try to add an answer with usable code shortly. — Yoshi, Sep 19 '16 at 07:03
Btw. note that Regex is not supported for all engines, and thus you need and extension to make it available for at least mysql. See: https://github.com/beberlei/DoctrineExtensions — Yoshi, Sep 19 '16 at 07:10

score 1 · Accepted Answer · answered Sep 19 '16 at 07:46

As I wrote in the comments you can't query against single array values when using doctrines array column. But as those columns require you not to use commas inside the array values, it is possible to write a query that utilizes this requirement.

Instead of regex (which would require an doctrine extension), you could also write a LIKE query, like so:

$query = 'Bar';

$qb = $this->companyRepository()->createQueryBuilder("c");
$companies = $qb
    ->where('c.type LIKE :only OR c.type LIKE :first OR c.type LIKE :last OR c.type LIKE :middle')
    ->setParameter('only', $query)
    ->setParameter('first', sprintf('%s%%,', $query))
    ->setParameter('last', sprintf('%%,%s', $query))
    ->setParameter('middle', sprintf('%%,%s,%%', $query))
    ->getQuery()
    ->getResult()
;

dump($companies);

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

Here is a similar question with an answer for you.

Symfony2 Doctrine querybuilder where IN

However, I would suggest using DQL if it's an option or just using Doctrine with PDO instead of QueryBuilder.

edited May 23 '17 at 12:31

Community

1
1

answered Sep 16 '16 at 15:35

John B

159
3
14

I think you misread my question, it's not about trying to do a Where In. – abernard Sep 19 '16 at 06:47

How to do a WHERE when the column is an array?

2 Answers2