MySql query works but syntax error in DQL

Question

This query in mySql gives me the result I want:

SELECT reportcolumn.name 
FROM report 
LEFT Join reportcolumn 
ON report.id=reportcolumn.reportid 
WHERE report.title = "Report"

However, when I try to write it as DQL, I get a syntax error.

        $report = "Reportname";
        $statement = 'SELECT c.name';
        $statement .= ' FROM AcmeDataBundle:Report r';
        $statement .= ' LEFT JOIN AcmeDataBundle:Reportcolumn c';
        $statement .= ' ON r.id=c.reportid';
        $statement .= ' WHERE r.title = :title';
        $em = $this->getDoctrine()->getManager();
        $query = $em->createQuery($statement)
        ->setParameters(array(
            'title'=> $report,
            ));
        $data = $query->getArrayResult();

How should I be handling the "ON" part of the statement. The relationship is defined, but if I leave that part of the statement out, I get obviously wrong results.

Thanks, Manisha

Answer 1

After doing more research in other questions, it appears that the problem is that you can't use "ON" in dql. Instead, "WITH" is the appropriate command. And changing the query to this, returned the correct set of column names (the many table) related to the selected report.

                $report = "Reportname";
        $statement = 'SELECT c.name';
        $statement .= ' FROM AcmeDataBundle:Reportcolumn c'; //select from the many table
        $statement .= ' LEFT JOIN AcmeDataBundle:Report r';
        $statement .= ' WITH r.id=c.reportid'; //use WITH not ON
        $statement .= ' WHERE r.title = :title';
        $em = $this->getDoctrine()->getManager();
        $query = $em->createQuery($statement)
        ->setParameters(array(
            'title'=> $report,
            ));
        $data = $query->getArrayResult();