blahblah blahblah - 3 months ago 9
MySQL Question

MySql Doctrine: find if given variable is IN array property

I have class Task with

categories
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
categories
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";

Answer

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.'%');