CODEWITHSUNDEEP
sqlarraysdoctrinequery-builder
StockBreak
StockBreak

Reputation: 2885

Doctrine where array field contains element

I have an entity which has an array property:

/**
 * @var array
 *
 * @ORM\Column(name="provinces", type="array")
 */
private $provinces;

How can I make a query to extract all the entities containing a specific element? Something like this:

$entities = $this->createQueryBuilder('e')
    ->where($qb->expr()->contains('e.provinces', ':element'))
    ->setParameter('element', $element)
    ->getQuery()
    ->getResult()
;

Upvotes: 2

Views: 5644

Answers (1)

knetsi
knetsi

Reputation: 1631

First of all you have to understand what the Doctrine array type represents, so basically the Doctrine array type translates into a SQL text type which is simply a serialized string. So you could create a query using the doctrine expression like so your query would look something like this

$entities = $this->createQueryBuilder('e')
    ->where($qb->expr()->like('e.provinces', ':element'))
    ->setParameter('element', '%' . \serialize($element) . '%')
    ->getQuery()
    ->getResult()
;

which is the equivalent for SQL 'SELECT ... WHERE e.provinces LIKE "%' . \serialize($element) . '%";'

But because you included the [sql] tag in your question I would like to tell you that this is not the correct way to handle those kind of situations, your database violates the 1NF. In Web development it is usual thing to violate the normalization forms, you can see it done by Content Management Systems when they store configurations, but they do not try to query those configuration parameters. In your case, you wish to query for provinces so normally you should struggle to query indexed fields, to increase the performance of your queries.

So a solution could be creating a Province Entity and replacing the array type with a ManyToMany relation.

Upvotes: 4

Related Questions