php Symfony2 日期时间查询构建器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9493992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Symfony2 datetime queryBuilder
提问by artyomboyko
I have 2 DateTimeclasses in Symfony2 project. I have entity Stat, in which have $dateproperty.
我在 Symfony2 项目中有 2 个DateTime类。我有实体Stat，其中有$date属性。
/**
* @ORM\Column(type="date", length="11")
*/
protected $date;
I have to make queries using DateTimeobjects in createQueryBuilder. How can i do that ? For example:
我必须使用createQueryBuilder 中的DateTime对象进行查询。我怎样才能做到这一点 ？例如：
$date_from = new DateTime('2012-02-01');
$date_to = new DateTime('2012-02-15');
I need to get all rows from table stats(entity Stat) between $date_from and $date_to. How should i write my query with createQueryBuilder ? My current code is:
我需要从$date_from 和 $date_to 之间的表stats（实体Stat）中获取所有行。我应该如何使用 createQueryBuilder 编写查询？我目前的代码是：
$qb = $em->createQueryBuilder();
$query = $qb->select('s')
->from('ACME\MyBundle\Entity\Stat', 's')
->where('s.date >= :date_from')
->andWhere('s.date <= :date_to')
->setParameter('date_from', $date_from)
->setParameter('date_to', $date_to)
->getQuery();
回答by Rauni Lillemets
Benjamin's answer is correct, but it lacks one detail. This is the correct way to do it:
本杰明的回答是正确的，但缺少一个细节。这是正确的做法：
$qb->andWhere($qb->expr()->between('s.date', ':date_from', ':date_to'));
But to set parameters, I need to do like this:
但是要设置参数，我需要这样做：
$qb->setParameter('date_from', $date_from, \Doctrine\DBAL\Types\Type::DATETIME);
$qb->setParameter('date_to', $date_to, \Doctrine\DBAL\Types\Type::DATETIME);
If I omit the DATETIME types, I get the following error (see here):
如果省略 DATETIME 类型，则会出现以下错误（请参阅此处）：
Object of class DateTime could not be converted to string
无法将 DateTime 类的对象转换为字符串
I am using Doctrine DBAL 2.0.5, this behaviour might have changed in later versions of Doctrine.
我正在使用 Doctrine DBAL 2.0.5，这种行为可能在更高版本的 Doctrine 中发生了变化。
回答by Benjamin Lazarecki
A QueryBuilder is a good solution.
QueryBuilder 是一个很好的解决方案。
But you can use
但是你可以使用
->andWhere($qb->expr()->between('s.date', ':date_from', 'date_to'))
or
或者
->andWhere($qb->expr()->andX(array(
$qb->expr()->gte('s.date', ':date_from'),
$qb->expr()->lte('s.date', ':date_to'))
)
The $qb->expr()->andX is usefull if you don't want to have some WTF when you add some andWhere or orWhere.
$qb->expr()->andX 如果您在添加一些 andWhere 或 orWhere 时不想有一些 WTF，那么 $qb->expr()->andX 很有用。
Here you have doctrine2 documentation for queryBuilder
You can also use setParameters method for your parameters
您还可以对参数使用 setParameters 方法
->setParameters(array(
'date_from' => $date_from,
'date_to' => $date_to,
))
回答by Limon
I had a similar situation. I couldn't use ->setParameter because of how my code was built, so i put this $andX variable to "catch" all the conditions founded in the foreach loop (in this case i just wrote the one with the dates because other are not relevant right now).
我也有类似的情况。由于我的代码是如何构建的，我不能使用 ->setParameter，所以我把这个 $andX 变量“捕捉”了在 foreach 循环中建立的所有条件（在这种情况下，我只是用日期写了一个，因为其他是现在不相关）。
$this->qb = $this->em->createQueryBuilder();
$andX = $this->qb->expr()->andX();
$this->qb->select('u')
->from('models\User','u');
foreach($data as $key=>&$value){
if($key == 'date'){
$from = $this->qb->expr()->gte('u.date_from',$this->qb->expr()->literal($value['datefrom']));
$to = $this->qb->expr()->lte('u.date_to',$this->qb->expr()->literal($value['dateto']));
$condition = $from. ' AND ' .$to;
$andX->add($condition);
}
//other if
}
Notice that the parameter for this function is a multidimensional array. Hope this is helpful.
请注意，此函数的参数是一个多维数组。希望这是有帮助的。
