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

Verdi

Just a question until the experts come - do you need to make those dates strings? I mean for example the first generated WHERE has r0_.fecha > 2012 - 07 -16 - should it not look more like r0_.fecha > '2012-07-16' or similar? – Freda

Totally agreed with you. But take a look to updated post – Verdi

Hmm ok - and what happens when you quote anyway? – Freda

Well, surely in my insane moment I made a mistake or typo. Quoting the date will not affect the query :D (as I thought at first). – Verdi

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

Moyer

+2 for parameters. Not using parameters is very dangerous as Doctrine can't escape them properly otherwise – Davao

This might break compatibility with certain DB platforms, like Microsoft SQL Server, where there's no Date type and everything is saved in the form of "Y-m-d H:i:s.000". You should let Doctrine handle the conversion by defining the type as the 3rd parameter of setParameter() like setParameter('monday', $monday, \Doctrine\DBAL\Types\Type::DATE). – Overact

The comparison operators to simulate BETWEEN are >= and <=, as opposed to > and <. – Ilex

Note that whilst BETWEEN and >=/<= are logically equivalent, you may find that your queries will perform better under MySQL when you use BETWEEN -- it encourages the query planner to make better choices on how to use related indexes. – Ilex

You could also use: ->where($qb->exp()->between('e.fecha', ':monday', ':sunday') – Cardoon

@MatíasNavarroCarter : ->where($qb->expr()->between('e.fecha', ':monday', ':sunday') you missed the r in expr() – Carvalho

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

Davao

Thank you so much for the answer... this is the only way for date comparison.... none of the ways given in other answer worked for me :) – Farley

Thanks - this really is the only truly platform independent way of doing date comparisons in Doctrine. – Davao

I agree but shouldn't it be ->setParameters(array(':from' => $thirtyDaysAgo, ...? – Crosstie

@AndreschSerj: Actually it should not. See doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/… . – Quita

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

Freda

I took your second option and works perfect, this stupid query took all my afternoonn wow. Nice, thank you so much! – Verdi

This is actually a very wrong way of doing it, you should never concatenate your variables straight into the query - you should really use parameters instead: docs.doctrine-project.org/projects/doctrine-dbal/en/latest/… – Davao

although it does work, this is idd wrong and even dangerous, better just remove the answer altogether... – Alexandro

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

Intension