Reputation: 4397
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
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