php - 切换到学说 : how to realize such query?

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

如您所见，它获取当前工作日(以及工作日/周末)的每日任务和当天的事件。现在我有两个 Doctrine 实体 - 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 不支持这样的 Union(它只是显示前导括号 '(' )的错误，并且看起来它根本就没有括号问题(在嵌套 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