Michael Laffargue Michael Laffargue - 7 months ago 29
SQL Question

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 !

Answer

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");
Comments