MySql Doctrine: find if given variable is IN array

Question

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 1

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