mySql中的这个查询给了我想要的结果:
SELECT reportcolumn.name
FROM report
LEFT Join reportcolumn
ON report.id=reportcolumn.reportid
WHERE report.title = "Report"
然而,当我尝试将它写成DQL时,我会遇到语法错误。
$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();
我应该如何处理声明中的"ON"部分。关系是有定义的,但如果我把陈述的这一部分省略掉,我会得到明显错误的结果。
谢谢,Manisha
AcmeDataBundle:Report
AcmeDataBundle:Reportcolumn
应该是
AcmeDataBundle.Report
AcmeDataBundle.Reportcolumn
除非这些是真实的表名,在这种情况下,您需要用后引号引用表名。
在对其他问题进行了更多研究后,问题似乎是你不能在dql中使用"ON"。相反,"WITH"是合适的命令。将查询更改为此,返回与所选报表相关的正确列名集(多表)。
$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();