php - manytomany - symfony 4 doctrine

Symfony2-Doctrine-De SQL a DQL

Question

Estoy trabajando en un proyecto PHP basado en Symfony2 y quiero que esto a continuación funcione en Symfony2 con Doctrine, pero en realidad obtengo este error usando "... FROM xx JOIN (SELECT ...."):

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

La declaración final de DQL que quiero hacer funcionar es:

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

Problema real: Doctrine / Symfony no registra el "... FROM xx JOIN (SELECT ...."

Answer 1

prueba esto

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