php 在学说 2 中选择日期之间的条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11553183/
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
Select entries between dates in doctrine 2
提问by manix
I will go insane with this minimal error that I'm not getting fix. I want to select entries between two days, the examples below ilustrate all my fails:
我会因为这个我没有得到修复的最小错误而发疯。我想在两天之间选择条目，下面的例子说明了我所有的失败：
opt 1.
选择 1。
$qb->where('e.fecha > ' . $monday->format('Y-m-d'));
$qb->andWhere('e.fecha < ' . $sunday->format('Y-m-d'));
result (0 entries):
结果（0 个条目）：
SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2
FROM reservacion r0_
WHERE (r0_.fecha > 2012 - 07 - 16) AND (r0_.fecha < 2012 - 07 - 22)
opt 2
选项 2
$qb->add('where', 'e.fecha between 2012-01-01 and 2012-10-10');
result (0 entries):
结果（0 个条目）：
SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2
FROM reservacion r0_ WHERE r0_.fecha
BETWEEN 2012 - 01 - 01 AND 2012 - 10 - 10
This is my table with current entries:
这是我的当前条目表：
id fecha cliente
1 2012-07-16 00:00:00 2
2 2012-07-16 13:00:00 4
3 2012-07-22 23:00:00 4
Edit 1
编辑 1
In order to evaluate the sql to avoid doubts, I ran this query:
为了评估sql避免疑惑，我运行了这个查询：
$qb->where('e.fecha > ' . $sunday->format('Y-m-d'));
result (3 entries):
结果（3 个条目）：
SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2
So, looks like the sql is not the problem. FROM reservacion r0_ WHERE r0_.fecha > 2012 - 07
所以，看起来sql不是问题。从保留 r0_ 哪里 r0_.fecha > 2012 - 07
回答by MacDada
You can do either…
你可以这样做…
$qb->where('e.fecha BETWEEN :monday AND :sunday')
->setParameter('monday', $monday->format('Y-m-d'))
->setParameter('sunday', $sunday->format('Y-m-d'));
or…
或者…
$qb->where('e.fecha > :monday')
->andWhere('e.fecha < :sunday')
->setParameter('monday', $monday->format('Y-m-d'))
->setParameter('sunday', $sunday->format('Y-m-d'));
回答by Harry Mustoe-Playfair
I believe the correct way of doing it would be to use query builder expressions:
我相信正确的做法是使用查询构建器表达式：
$now = new DateTimeImmutable();
$thirtyDaysAgo = $now->sub(new \DateInterval("P30D"));
$qb->select('e')
->from('Entity','e')
->add('where', $qb->expr()->between(
'e.datefield',
':from',
':to'
)
)
->setParameters(array('from' => $thirtyDaysAgo, 'to' => $now));
http://docs.doctrine-project.org/en/latest/reference/query-builder.html#the-expr-class
http://docs.doctrine-project.org/en/latest/reference/query-builder.html#the-expr-class
Edit: The advantage this method has over any of the other answers here is that it's database software independent - you should let Doctrine handle the date type as it has an abstraction layer for dealing with this sort of thing.
编辑：此方法相对于此处任何其他答案的优势在于它独立于数据库软件 - 您应该让 Doctrine 处理日期类型，因为它具有用于处理此类事情的抽象层。
If you do something like adding a string variable in the form 'Y-m-d' it will break when it goes to a database platform other than MySQL, for example.
例如，如果您执行诸如以“Ymd”形式添加字符串变量之类的操作，当它转到 MySQL 以外的数据库平台时会中断。
回答by azhrei
EDIT: See the other answers for better solutions
EDIT: See the other answers for better solutions
The original newbie approaches that I offered were (opt1):
我提供的原始新手方法是（opt1）：
$qb->where("e.fecha > '" . $monday->format('Y-m-d') . "'");
$qb->andWhere("e.fecha < '" . $sunday->format('Y-m-d') . "'");
And (opt2):
和（选项2）：
$qb->add('where', "e.fecha between '2012-01-01' and '2012-10-10'");
That was quick and easy and got the original poster going immediately.
这既快速又简单，并立即得到了原始海报。
Hence the accepted answer.
因此，接受的答案。
As per comments, it is the wrong answer, but it's an easy mistake to make, so I'm leaving it here as a "what not to do!"
根据评论，这是错误的答案，但这是一个容易犯的错误，所以我将其留在这里作为“不该做什么！”
回答by Laurent Lolo
Look how I format my date $jour in the parameters. It depends if you use a expr()->like or a expr()->lte
看看我如何在参数中格式化我的日期 $jour。这取决于你使用的是 expr()->like 还是 expr()->lte
$qb
->select('e')
->from('LdbPlanningBundle:EventEntity', 'e')
->where(
$qb->expr()->andX(
$qb->expr()->orX(
$qb->expr()->like('e.start', ':jour1'),
$qb->expr()->like('e.end', ':jour1'),
$qb->expr()->andX(
$qb->expr()->lte('e.start', ':jour2'),
$qb->expr()->gte('e.end', ':jour2')
)
),
$qb->expr()->eq('e.user', ':user')
)
)
->andWhere('e.user = :user ')
->setParameter('user', $user)
->setParameter('jour1', '%'.$jour->format('Y-m-d').'%')
->setParameter('jour2', $jour->format('Y-m-d'))
->getQuery()
->getArrayResult()
;
