CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Javad
Javad

Reputation: 4397

How to use DATE_ADD to compare date in range in Doctrine and Symfony2 using createQueryBuilder

I have two tables related to each other (main_table OneToMay detail_table). There is a deadline_days field in main table and a create_date in detail_table. I want to select all details which create_date+main.deadline_days are passed base on today date. (This was the scenario)

This is the proper MySQL query which gives me right records

SELECT  `D`.* FROM `details_table` AS  `D` 
INNER JOIN  `main_table` AS  `M` ON (`D`.`Main_Id` = `M`.`id`) 
WHERE DATE_ADD(`D`.`Create_Date`, INTERVAL  `M`.`Deadline_days` DAY) <= NOW()

Now in Symfony when I want to create the query using createQueryBuilder it comes with this error

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

This is what I have for now in my query builder

$result = $this->createQueryBuilder('D')
->join('D.Main', 'M')
->where('DATE_ADD(D.Create_Date, INTERVAL M.DeadLine_Days DAY) <= NOW()')
->getQuery()
->getResult();

Any idea how to fix this? Thanks in advance.

Please do not suggest using native query

Upvotes: 2

Views: 4273

Answers (1)

Javad
Javad

Reputation: 4397

This is what I found base on this link (Doctrine Update DQL function signature)
Doctrine2 has function DATE_ADD but does not have INTERVAL param as MySQL, also the unit param should be as string 'DAY'; so the Query should be:

$result = $this->createQueryBuilder('D')
   ->join('D.Main', 'M')
   ->where('DATE_ADD(D.Create_Date, M.DeadLine_Days, 'DAY') <= CURRENT_DATE()')
   ->getQuery()
   ->getResult();

In doctrine we should use CURRENT_DATE() instead of NOW()

Upvotes: 5

Related Questions