Symfony2-主义-从SQL到DQL [英] Symfony2 - Doctrine - From SQL to DQL

问题描述

我正在基于Symfony2的PHP项目上工作，我希望下面的内容可以在Doctrine的Symfony2上工作，但是实际上我通过使用 ... FROM xx JOIN（SELECT ....）来收到此错误：

I'm working on PHP project based on Symfony2 and I want this below to works on Symfony2 by Doctrine but actually I get this error by using the "...FROM xx JOIN (SELECT...." :

[Semantical Error] line 0, col 176 near '(
SELECT': Error: Class '(' is not defined.

我要执行的最终DQL语句是：

The final DQL statement that I want to make work is :

return $this->getEntityManager()
                ->createQuery(
            "SELECT u.username, m1.id, n.id as notification, n.vue, u.lastActivity, LEFT(m1.message,60) as Message, m1.lu, m1.dateEnvoi, i.image
            FROM MCoreBundle:Messages m1 
            JOIN (
                SELECT m.from, max(dateEnvoi) as dateEnvoi 
                FROM MCoreBundle:Messages m
                JOIN MCoreBundle:User u on m.from = u.id
                WHERE m.to = :user
                GROUP BY m.from
            ) t ON m1.from = t.from AND 
            m1.dateEnvoi = t.dateEnvoi, 
            MCoreBundle:User u, 
            MCoreBundle:Notification n, 
            MCoreBundle:Images i WHERE 
                  u.id = m.from AND 
                  n.id = m.notification AND 
                  i.user = u.id")
                ->setParameters(array('user' => $iIDUser))
                ->getResult();

实际问题：
Doctrine / Symfony不会重新整理 ... FROM xx JOIN（SELECT ....

Actual problem : Doctrine/Symfony doesnt recorgnize the "...FROM xx JOIN (SELECT...."

推荐答案

尝试一下

SELECT u.username, m1.id, n.id as notification, n.vue, u.lastActivity, LEFT(m1.message,60) as Message, m1.lu, m1.dateEnvoi, i.image
FROM MCoreBundle:Messages m1,
MCoreBundle:User u, 
MCoreBundle:Notification n, 
MCoreBundle:Images i 
JOIN (
  SELECT m.from, max(dateEnvoi) as dateEnvoi 
  FROM MCoreBundle:Messages m
  JOIN MCoreBundle:User u on m.from = u.id
  WHERE m.to = :user
  GROUP BY m.from
) t ON m1.from = t.from AND m1.dateEnvoi = t.dateEnvoi
WHERE u.id = m.from AND n.id = m.notification AND i.user = u.id

这篇关于Symfony2-主义-从SQL到DQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！