在学说2中的日期之间选择条目

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

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不是问题。 FROM reservacion r0_ WHERE r0_.fecha> 2012 - 07

4 个解决方案

#1

114

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'));

#2

I believe the correct way of doing it would be to use query builder expressions:

我相信这样做的正确方法是使用查询构建器表达式：

$now = new DateTime();
$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

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.

如果您执行类似于以“Y-m-d”形式添加字符串变量的操作，例如，当它转到MySQL以外的数据库平台时会中断。

#3

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):

并且（opt2）：

$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!"

根据评论，这是错误的答案，但这是一个容易犯的错误，所以我把它留在这里作为“什么不该做！”

#4

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()
    ;

#1

114