INTERVAL 1 MONTH not working With symfony2 doctrine?

Question

I am stuck here and i spend last 2 days resolving this issue but failed. I am writing a query inside my repository to get the entries for current month. here is my query:-

$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 didn't helped 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 have to remember that DQL is not SQL. The error comes from Doctrine's Lexer and not from MySQL. DQL doesn't support INTERVAL (see list of supported functions).

Read more on adding your own functions, specifically adding DATE_ADD with INTERVAL support here: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html#date-add