Doctrine DQL Date as parameter problem

Question

Hi there I got a DQL that works (I get all my event since the beginning) :

DoctrineHelper::getEntityManager()->createQueryBuilder()
    ->select("u.surname, count(u.surname) as total")
    ->from("User", "u")
    ->from("AbstractEvent", "e")
    ->from("Attendance", "a")
    ->where("u = a.attendee")
    ->andWhere("e = a.event")
    ->andWhere("a.status=1")
    ->andWhere("e.date<CURRENT_TIMESTAMP()")
    ->groupBy("u.email")
    ->orderBy("total","desc");

But this one doesn't (I would like only this month event):

DoctrineHelper::getEntityManager()->createQueryBuilder()
    ->select("u.surname, count(u.surname) as total")
    ->from("User", "u")
    ->from("AbstractEvent", "e")
    ->from("Attendance", "a")
    ->where("u = a.attendee")
    ->andWhere("e = a.event")
    ->andWhere("a.status=1")
    ->andWhere("e.date<CURRENT_TIMESTAMP()")
    ->andWhere("e.date>?", date('Y-m-d 00:00:00', strtotime('-'.(date('j')-1).' day')) )
    ->groupBy("u.email")
    ->orderBy("total","desc");

My error.log has this line :

 #0 /var/www/Doctrine/ORM/Query/AST/InputParameter.php(46): Doctrine\\ORM\\Query\\QueryException::invalidParameterFormat('?')

A print_r of the date gives me : 2011-08-01 00:00:00 which is correct.

Here is the mapping for date:

/**
     * Date of the event.
     * @Column(type="datetime")
     */
    private $date;

Any help appreciated thanks !

Answer 1

Use a query parameter and then set the DateTime object as value of the parameter. Doctrine will then handle the conversion automatically.

Here is the example code from my own project

    $builder = $this->getEntityManager()->createQueryBuilder();
    $builder->select('a')
        ->from('MaggotsLeadGeneratorBundle:JobApplication', 'a')
    ;

    $and = $builder->expr()->andX();
    if ($dateFrom) {
        $and->add($builder->expr()->gt('a.ctime', ':dateFrom'));
        $builder->setParameter('dateFrom', $dateFrom);
    }

    if ($dateTo) {
        $and->add($builder->expr()->lt('a.ctime', ':dateTo'));
        $builder->setParameter('dateTo', $dateTo);
    }

    $builder->where($and);
    $query = $builder->getQuery();

    $result = $query->getResult();