php Doctrine DQL 日期作为参数问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7038305/
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
Doctrine DQL Date as parameter problem
提问by Michael Laffargue
Hi there I got a DQL that works (I get all my event since the beginning) :
嗨，我得到了一个有效的 DQL（我从一开始就得到了我的所有事件）：
DoctrineHelper::getEntityManager()->createQueryBuilder()
->select("u.surname, count(u.surname) as total")
->from("User", "u")
->from("AbstractEvent", "e")
->from("Attendance", "a")
->where("u = a.attendee")
->andWhere("e = a.event")
->andWhere("a.status=1")
->andWhere("e.date<CURRENT_TIMESTAMP()")
->groupBy("u.email")
->orderBy("total","desc");
But this one doesn't (I would like only this month event):
但是这个没有（我只想要这个月的活动）：
DoctrineHelper::getEntityManager()->createQueryBuilder()
->select("u.surname, count(u.surname) as total")
->from("User", "u")
->from("AbstractEvent", "e")
->from("Attendance", "a")
->where("u = a.attendee")
->andWhere("e = a.event")
->andWhere("a.status=1")
->andWhere("e.date<CURRENT_TIMESTAMP()")
->andWhere("e.date>?", date('Y-m-d 00:00:00', strtotime('-'.(date('j')-1).' day')) )
->groupBy("u.email")
->orderBy("total","desc");
My error.log has this line :
我的 error.log 有这一行：
#0 /var/www/Doctrine/ORM/Query/AST/InputParameter.php(46): Doctrine\ORM\Query\QueryException::invalidParameterFormat('?')
A print_r of the date gives me : 2011-08-01 00:00:00 which is correct.
日期的 print_r 给了我： 2011-08-01 00:00:00 这是正确的。
Here is the mapping for date:
这是日期的映射：
/**
* Date of the event.
* @Column(type="datetime")
*/
private $date;
Any help appreciated thanks !
任何帮助表示感谢！
采纳答案by Michael Laffargue
Couldn'y make it work with the parameter so here is what I came to finally :
不能让它与参数一起工作，所以这就是我最终得出的结论：
$now = new DateTime;
$now->modify( '-'.(date('j')-1).' day' );
$qb = DoctrineHelper::getEntityManager()->createQueryBuilder()
->select("u.surname, count(u.surname) as total")
->from("User", "u")
->from("AbstractEvent", "e")
->from("Attendance", "a")
->where("u = a.attendee")
->andWhere("e = a.event")
->andWhere("a.status=1")
->andWhere("e.date<CURRENT_TIMESTAMP()")
->andWhere("e.date > '".$now->format("Y-m-d H:i:s")."'")
->groupBy("u.email")
->orderBy("total","desc");
回答by Entea
Use a query parameter and then set the DateTime object as value of the parameter. Doctrine will then handle the conversion automatically.
使用查询参数，然后将 DateTime 对象设置为该参数的值。Doctrine 然后会自动处理转换。
Here is the example code from my own project
这是我自己项目中的示例代码
$builder = $this->getEntityManager()->createQueryBuilder();
$builder->select('a')
->from('MaggotsLeadGeneratorBundle:JobApplication', 'a')
;
$and = $builder->expr()->andX();
if ($dateFrom) {
$and->add($builder->expr()->gt('a.ctime', ':dateFrom'));
$builder->setParameter('dateFrom', $dateFrom);
}
if ($dateTo) {
$and->add($builder->expr()->lt('a.ctime', ':dateTo'));
$builder->setParameter('dateTo', $dateTo);
}
$builder->where($and);
$query = $builder->getQuery();
$result = $query->getResult();
回答by J0HN
I think it could be because your e.dateis mapped as datetime. Try passing DateTime object instead of timestamp.
我认为这可能是因为您e.date被映射为datetime. 尝试传递 DateTime 对象而不是时间戳。
//...
->andWhere("e.date>?", new DateTime("2011-08-01 00:00:00"))
//...
Please, post your entity mapping, this would help clarifying the source of your error.
请发布您的实体映射，这将有助于澄清您的错误来源。
Update:strange... Maybe this will work:
更新：奇怪......也许这会奏效：
->andWhere("e.date>'?'", new DateTime("2011-08-01 00:00:00")->format("Y-m-d H:i:s"))
回答by Michael
For me this one worked, to get all results within the last 30 minutes:
对我来说，这是一个有效的方法，可以在过去 30 分钟内获得所有结果：
$qb = $em->createQueryBuilder();
$qb->select('u')
->from('User', 'u')
->where('u.group = '.$this->group->getId())
->andWhere("u.createdAt > :date")
->setParameter('date', new \DateTime(date("F d Y H:i:s", time() - 30*60)));
