MySql Doctrine:查找给定变量是否为 IN 数组属性

2022-01-03 00:00:00 arrays php mysql symfony doctrine-orm

我有带有 categories 整数属性数组的 Task 类

I have class Task with categories array of integers property

class Task{
      /**
       * @var array
       *
       * @ORMColumn(name="categories", type="array", nullable=true)
       */
private $categories;
 }

现在在控制器中，我正在尝试构建查询，该查询将检查 category id 变量是否在任务的 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);

这给了我错误:

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

推荐答案

据我所知，这在 Doctrine 中是不可能的，因为在从数据库中反序列化之前，数组在技术上不是数组.

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.

我知道获得您要查找的结果的唯一方法是将您的数据库值视为一个字符串，并使用带有通配符的 like 在该值中搜索所需的字符串.

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

相关文章