Syntax Error Symfony2 Query

Question

Hi I have following join query.

return $this->getEntityManager()
        ->createQuery(
            "SELECT t1.taskId,t1.title,t1.dueDate,t1.modified,cb.forename,cb.surname,up.profilePhotoPath,
            (SELECT tp.metaData,tp.content FROM AppBundle:J1TaskPost tp order by tp.created DESC ) AS taskPost
            FROM AppBundle:J1Task as t1
            JOIN t1.assignees As ta
            JOIN t1.createdBy As cb
            JOIN AppBundle:J1UserProfile as up WITH t1.createdBy = up.user
            WHERE t1.deleted IS NULL and t1.status = false and ta.userid=$userId group by t1.taskId order by tp.created desc"
        )->getResult();

It is throwing the following error:

Syntax Error] line 0, col 130: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','.

I am not able to find out what is missing in above query.

Answer 1

The problem is in this line:

"SELECT t1.taskId,t1.title,t1.dueDate,t1.modified,cb.forename,cb.surname,up.profilePhotoPath,
(SELECT tp.metaData,tp.content FROM AppBundle:J1TaskPost tp order by tp.created DESC ) AS taskPost

You need to select the items within taskPost, but what you're doing here is to define the table instead. What you could do is:

SELECT t1.taskId,t1.title,t1.dueDate,t1.modified,cb.forename,cb.surname,up.profilePhotoPath, taskPost.metaData, taskPost.content FROM
(SELECT tp.metaData,tp.content FROM AppBundle:J1TaskPost tp order by tp.created DESC ) AS taskPost,
...