8

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.

Community
  • 1
  • 1
ScoRpion
  • 11,364
  • 24
  • 66
  • 89

2 Answers2

29

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'));
jkucharovic
  • 4,214
  • 1
  • 31
  • 46
  • Thanks a lot jkucharovic. It was so usefull. Can u plese tell me how can i do the same with INTERVAL 1 YEAR – ScoRpion Apr 06 '12 at 12:23
  • `$query->setParameter('date', new \DateTime('-1 year'));` – jkucharovic Apr 07 '12 at 09:18
  • and can u tell how can i use NOW here – ScoRpion Apr 07 '12 at 15:49
  • :- Can u tell me the both.. NOW() and TODAY – ScoRpion Apr 08 '12 at 08:11
  • _Now_ is simple – `$query->setParameter('date', new \DateTime('now'));`. But it's not usable, because it means “at this moment”. – jkucharovic Apr 09 '12 at 05:32
  • For use _Today_ you need to change your query to `SELECT … AND a.date >= :today AND <= :now` and parameters `$query->setParameter('today', new \DateTime('today')); $query->setParameter('now', new \DateTime('now'));` – jkucharovic Apr 09 '12 at 05:37
  • :- Sorry to disturb u again Sir, U know I am stuck again the same place. I want to fetch data from the first day of the month to current date. and also from first day of year to current date. How can i do it in doctrine. Sql does it this way http://stackoverflow.com/questions/7403390/select-records-from-start-of-month-to-current-date – ScoRpion Apr 14 '12 at 13:24
  • Query should be the same – `SELECT … AND a.date >= :firstDay AND <= :now` and parameter for _first day of month_ `new \DateTime('1.' . date('m.Y'))` and for _first day of year_ `new \DateTime('1.1.' . date('Y'))` – jkucharovic Apr 17 '12 at 08:07
9

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

Sithu
  • 4,752
  • 9
  • 64
  • 110
Jakub Zalas
  • 35,761
  • 9
  • 93
  • 125