postgresql 如何在 Doctrine2 查询生成器中使用“间隔”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11936677/
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
How to use 'interval' in Doctrine2 Query Builder
提问by mediafreakch
In my Symfony2 repository, I'd like to get objects from a schedule table, that have started, but not finished yet. The interval, within the objects should be encountered as 'not finished', should be passed as a variable.
在我的 Symfony2 存储库中，我想从计划表中获取已开始但尚未完成的对象。对象内的间隔应该被视为“未完成”，应该作为变量传递。
Using plain SQL, it works like this:
使用普通 SQL，它的工作方式如下：
SELECT * FROM slots rbs
WHERE rbs.rundate = '2012-08-13'
AND rbs.runtime <= '11:05:00'
AND '11:05:00' <= rbs.runtime + interval '300 seconds'
ORDER BY rbs.rundate DESC, rbs.runtime DESC
Can I achieve the same with DQL / Query Builder?
我可以使用 DQL / Query Builder 实现相同的目标吗？
This is what I have so far:
这是我到目前为止：
$qb = $this->createQueryBuilder('rbs');
$qb->where(
$qb->expr()->andX(
$qb->expr()->eq('rbs.rundate', ':date'),
$qb->expr()->lte('rbs.runtime', ':time'),
'rbs.runtime + interval 300 seconds >= :time'
)
)
->orderBy('rbs.rundate', 'DESC')
->addOrderBy('rbs.runtime', 'DESC')
->setParameter('date', date('Y-m-d'))
->setParameter('time', date('H:i:s'))
But this returns the following error:
但这会返回以下错误：
[Doctrine\ORM\Query\QueryException]
[Syntax Error] line 0, col 139: Error: Expected =, <, <=, <>, >, >=, !=, got '300'
I found that 'interval' is not supportedby Doctrine2 / DQL, which is also mentioned here.
我发现Doctrine2/DQL不支持“间隔”，这里也提到了这一点。
Any suggestions on how to accomplish this with Doctrine2's Query Builder or DQL (and passing the interval as variable)?
关于如何使用 Doctrine2 的查询生成器或 DQL（并将间隔作为变量传递）来实现这一点的任何建议？
回答by guillaumepotier
As far as I know, Interval is not ported in Doctrine. The workaround I found is to work directly on the DateTime I pass as a param (here, I'd like to use interval of 2 days, passed through Datetime):
据我所知，Interval 没有移植到 Doctrine 中。我发现的解决方法是直接在我作为参数传递的 DateTime 上工作（在这里，我想使用 2 天的间隔，通过 Datetime）：
public function findOngoingPublicEvents()
{
return $this->createQueryBuilder('e')
->where('e.isActive = 1')
->andWhere('e.isPublic = 1')
->andWhere('e.begin <= :begin')
->andWhere('e.end >= :end')
->orderBy('e.id', 'ASC')
->setParameter('begin', new \DateTime('+2 days'))
->setParameter('end', new \DateTime('-2 days'))
->getQuery()
->execute();
}
回答by Kiran
If you want to use INTERVAL (in Doctrine 2, DQL) on mysql comumn field, You can use as below,
如果您想在 mysql comumn 字段上使用 INTERVAL（在 Doctrine 2，DQL 中），您可以使用如下，
$qb->andWhere("DATE_ADD(pv.myDAte,48,'hour') >= UTC_TIMESTAMP()");
It will print SQL as below,
它将打印如下 SQL，
...... DATE_ADD(p0_.appointment_date, INTERVAL 48 HOUR) >= UTC_TIMESTAMP() .....
回答by Serhii Popov
@Kiran write only about DATE_ADD, but you can also use DATE_SUB
@Kiran 只写 about DATE_ADD，但你也可以使用DATE_SUB
$qb->andWhere("DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE(), 6, 'day')");
It is equivalent of SQL:
它相当于 SQL：
DATE(a2_.updatedAt) = DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)
