blahblah blahblah - 1 month ago 6x
MySQL Question

MySql Doctrine: find if given variable is IN array property

I have class Task with

array of integers property

class Task{
* @var array
* @ORM\Column(name="categories", type="array", nullable=true)
private $categories;

now in controller I am trying to build query which would check if
category id
variable is in
array of the task

$qb = $this->getDoctrine()->getRepository('CoreBundle:Task')->createQueryBuilder('t');
$qb->where(':category IN (t.categories)')
->setParameter('category', $category);

This gives me error:

[Syntax Error] line 0, col 140: Error: Expected Literal, got "t";


To the best of my knowledge this isn't possible in Doctrine directly as the array isn't technically an array until it has been unserialized from the database.

The only way I know to get the result you are looking for is to treat your database value as a string and search for the required string in that value using a like with wildcards.

$qb = $this->getDoctrine()->getRepository('CoreBundle:Task')->createQueryBuilder('t');
$qb->where('t.categories LIKE :category')
   ->setParameter('category', '%'.$category.'%');