转向学说:如何实现这种查询?

Question

我有一个在平面PHP上构建的TaskManager，现在我想在Symfony上构建它。

我有这样的查询字符串来填充当天的任务表：

(SELECT id, name, description, time, length
FROM daily 
WHERE 
(day = DAYNAME(:date)
OR day = 'all' 
OR (DAYNAME(:date) IN ('Sunday','Saturday') AND day = 'weekend' )
OR (DAYNAME(:date) NOT IN ('Sunday','Saturday') AND day = 'workday' ))
AND time IS NOT NULL)
UNION
(SELECT id, name, description,
DATE_FORMAT(date, '%H:%i') as time, length
FROM events
WHERE DATE_FORMAT(date, '%Y-%m-%d') = :date)
ORDER BY time

如您所见，它获取当前工作日(以及工作日/周末(的日常任务和当天的事件。 现在我有两个原则实体 - DailyTask 和 Event，有两个适当的表，我想将此查询转换为 DQL 以获取一个数组，其中 DailyTasks 和 Events 作为查询的结果。

问题 - 这样的技巧不起作用：

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery("(SELECT id, name, description, time, length
FROM OrganizerBundle:DailyTask 
WHERE 
(day = DAYNAME(:date)
OR day = 'all' 
OR (DAYNAME(:date) IN ('Sunday','Saturday') AND day = 'weekend' )
OR (DAYNAME(:date) NOT IN ('Sunday','Saturday') AND day = 'workday' ))
AND time IS NOT NULL)
UNION
(SELECT id, name, description,
DATE_FORMAT(date, '%H:%i') as time, length
FROM OrganizerBundle:Event
WHERE DATE_FORMAT(date, '%Y-%m-%d') = :date)
ORDER BY time")->setParameter('date', $day);

据我所知，Doctrine 不支持这样的联合(它只是显示前导括号"('(的错误(，并且似乎它根本没有括号问题(在嵌套 OR 条件下等(。

这里最好的解决方案是什么？到目前为止，我没有看到使用 Doctrine :( 的任何优势 - 它只会让事情变得困难，并导致我在 PHP 上使用一些可以使用 MySQL 引擎的解决方案(日期格式、条件、排序等(

Answer 1

最简单的方法是将其移动到子查询中。无论如何，这实际上是您已经通过在上一个)之外放置ORDER BY来做的事情。

SELECT  -- notice how I simply put the SELECT statement outside
* -- you're grabbing all of the unioned content.
FROM
(
-- I cleared away your spare `()` from in here and re-indented for clarity.
SELECT id, name, description, time, length
FROM daily 
WHERE (
day = DAYNAME(:date)
OR day = 'all'
OR (DAYNAME(:date) IN ('Sunday','Saturday') AND day = 'weekend' )
OR (DAYNAME(:date) NOT IN ('Sunday','Saturday') AND day = 'workday' )
)
AND time IS NOT NULL
UNION
SELECT id, name, description, DATE_FORMAT(date, '%H:%i') as time, length
FROM events
WHERE DATE_FORMAT(date, '%Y-%m-%d') = :date
) subqry -- This lets SQL know that all of the above 
-- needs to be viewed as "one table"
ORDER BY time