Symfony2 - Doctrine - From SQL to DQL

Question

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.

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

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

are you joining MCoreBundle:User u, MCoreBundle:Notification n, MCoreBundle:Images i. if yes u have to write join every time you join a table. — sonam
Tryed that but it doesnt really solve my problem, still getting the same error ...'( SELECT': Error: Class '(' is not defined. — Youssef El Gharbaoui

sonam sonam · Accepted Answer · 2014-01-19T01:51:12

try this

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 - Doctrine - From SQL to DQL

1 Answers