Select entries between dates in doctrine 2

Question

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.

$qb->where('e.fecha > ' . $monday->format('Y-m-d'));
$qb->andWhere('e.fecha < ' . $sunday->format('Y-m-d'));

result (0 entries):

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

$qb->add('where', 'e.fecha between 2012-01-01 and 2012-10-10');

result (0 entries):

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:

$qb->where('e.fecha > ' . $sunday->format('Y-m-d'));

result (3 entries):

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

Answer 1

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

Answer 2

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

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.

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.

--- another example:

This example makes a between condition by using the greater than and lesser than approach.

if ($updateDateTime instanceof DateTime) {
    $qb->andWhere(
        $qb->expr()->gte('c.updated', ':updateDateTimeStart'),
        $qb->expr()->lt('c.updated', ':updateDateTimeEnd'),
    );

    $updateDateTimeImmutable = DateTimeImmutable::createFromMutable($updateDateTime);
    $start = $updateDateTimeImmutable->setTime(0,0,0, 0);
    $end = $start->modify('+1 day');

    $qb->setParameter('updateDateTimeStart', $start, Types::DATE_IMMUTABLE);
    $qb->setParameter('updateDateTimeEnd', $end, Types::DATE_IMMUTABLE);
}

Answer 3

    EDIT: See the other answers for better solutions

The original newbie approaches that I offered were (opt1):

$qb->where("e.fecha > '" . $monday->format('Y-m-d') . "'");
$qb->andWhere("e.fecha < '" . $sunday->format('Y-m-d') . "'");

And (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!"

Answer 4

Look how I format my date $jour in the parameters. It depends if you use a expr()->like or a 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()
    ;