查询生成器在字段类型数组上添加条件 [英] query builder add condition on field type array

问题描述

我的问题很简单：是否可以使用doctrine和查询生成器在字段类型数组上添加where语句？

My question is simple : is it possible to add a where statment using doctrine and the query builder on a field type array ?

在我的实体内部，我有以下内容：

Inside my entity I have the following :

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

在对象视图中，数组看起来像这样：

In the object view the array look like this:

array(
  1 => false,
  2 => false,
  3 => true,
  4 => false,
  5 => false,
  6 => false,
  7 => false
);

序列化并插入数据库后，它的表示形式如下：

an it's representation once serialize and inserted into the database look like this :

a:7:{i:1;b:0;i:2;b:0;i:3;b:1;i:4;b:0;i:5;b:0;i:6;b:0;i:7;b:0;}

我想要达到的目标是这样的：

What I try to achieve is something like this :

   $q = $this->em->getRepository('AcmeBundle:Notification')
        ->createQueryBuilder('n')
        ->andWhere('e.weekDays = :day')    <-- This is wrong
        ->setParameter('day', date('N'))
    ;

通常这会在SQL中导致类似这样的情况

typically this would result to something like this in SQL

SELECT * FROM notification WHERE weekDays LIKE '%i:1;b:1%' -- if date('N') = 1 (monday)
SELECT * FROM notification WHERE weekDays LIKE '%i:7;b:1%' -- if date('N') = 7 (sunday)

和
SELECT * FROM notification WHERE weekDays LIKE'％i：1; b：0％'
以防我要设置- > andWhere（'e.weekDays！=：day'）

推荐答案

使用2.5版原理和symfony的3.0+版本，看起来可能像这样：

Using version 2.5 of doctrine and 3.0+ of symfony it might look something like:

$qb = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n');
$qb->where($qb->expr()->like('n.weekDays',':weekDays'))
    ->setParameter('weekDays','%i:'.date('N').';b:1%');
$results = $qb->getQuery()->getResult();

可能也有效的旧方法：

$q = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n')
    ->andWhere("e.weekDays LIKE '%:day%'")
    ->setParameter('day', 'i:'.date('N').';b:1')
;

如果您想对数据进行一些不同的设置，则可以将字段分为7个，因为工作日不是即将更改：

If you wanted to structure your data a little different you could separate that field into 7 since weekdays are not going to change:

//Entity
/**
 * @ORM\Column(type="boolean")
 */
 protected $monday;

/**
 * @ORM\Column(type="boolean")
 */
 protected $tuesday;

//And so on

然后类似：

$weekday = strtolower(date('l')); //Lowercase "L"
$q = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n')
    ->andWhere('n.'.$weekday. '= 1');

这篇关于查询生成器在字段类型数组上添加条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！