如何使用 DATE_ADD 使用 createQueryBuilder 比较 Doctrine 和 Symfony2 范围内的日期 [英] How to use DATE_ADD to compare date in range in Doctrine and Symfony2 using createQueryBuilder

问题描述

我有两个相互关联的表(main_table OneToMay detail_table).主表中有一个 deadline_days 字段，detail_table 中有一个 create_date 字段.我想选择根据今天日期传递 create_date+main.deadline_days 的所有详细信息.(这是场景)

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)

这是正确的 MySQL 查询，它给了我正确的记录

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()

现在在 Symfony 中，当我想使用 createQueryBuilder 创建查询时，它会出现此错误

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.

请不要建议使用原生查询

推荐答案

这是我根据此链接找到的内容 (Doctrine 更新 DQL 函数签名)
Doctrine2 有函数 DATE_ADD 但没有 MySQL 的 INTERVAL 参数，单元参数也应该是字符串 'DAY';所以查询应该是:

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();

理论上我们应该使用 CURRENT_DATE() 而不是 NOW()

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

这篇关于如何使用 DATE_ADD 使用 createQueryBuilder 比较 Doctrine 和 Symfony2 范围内的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！