INTERVAL 1 MONTH not working with symfony2 doctrine?

Question

I am stuck here and I spent the last 2 days solving this problem, but could not. I am writing a request in my repository to get entries for the current month. here is my request: -

$this->getEntityManager() ->createQuery('SELECT count(a) FROM CollegeStudentBundle:StudentAttendance a where a.student_id='.$id.' and a.date > DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 1 MONTH)') 

When I try to run this, it gives me an error

 [Syntax Error] line 0, col 133: Error: Expected Doctrine\ORM\Query\Lexer::T_COMMA, got '1' 

Even I tried this thing , but it didn’t help me.

Answer 1

You should use parameter binding:

 $query = $em->createQuery('SELECT count(a) FROM CollegeStudentBundle:StudentAttendance a where a.student_id = :id and a.date > :date'); $query->setParameter('id', $id); $query->setParameter('date', new \DateTime('-1 month')); 

Answer 2

You must remember that DQL is not SQL. The error comes from Doctrine Lexer, not from MySQL. DQL does not support INTERVAL (see the list of supported functions ).

For more information, add your own functions , in particular by adding DATE_ADD with INTERVAL support: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html#date -add