CODEWITHSUNDEEP
symfonydoctrinequery-builder
abernard
abernard

Reputation: 231

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

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)

Upvotes: 0

Views: 1184

Answers (2)

Yoshi
Yoshi

Reputation: 54659

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);

Upvotes: 1

John B
John B

Reputation: 169

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.

Upvotes: 0

Related Questions