phpmysqldoctrine-ormdql

Doctrine DQL Date as parameter problem


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 !


Solution

  • Couldn'y make it work with the parameter so here is what I came to finally :

    $now = new DateTime;
    $now->modify( '-'.(date('j')-1).' day' );
    
    $qb = 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 > '".$now->format("Y-m-d H:i:s")."'")
                            ->groupBy("u.email")
                            ->orderBy("total","desc");