I try to execute this query in my CompanyRepository
$qb = $this->_em->createQueryBuilder();
$qb->select(array('c', 'ld'))
->from('Model\Entity\Company', 'c')
->leftJoin('c.legaldetails', 'ld', \Doctrine\ORM\Query\Expr\Join::ON, 'c.companyid=ld.companyid');
$query = $qb->getQuery();
echo($query->getSQL());
When I try to do it I having error:
Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 69: Error: Expected end of string, got 'ON'' in /home/raccoon/web/freetopay.dev/www/class/new/library/Doctrine/ORM/Query/QueryException.php on line 42
These are my models:
<?php
namespace Model\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
/**
* Company
*
* @ORM\Table(name="Company")
* @ORM\Entity(repositoryClass="\Model\Repository\CompanyRepository")
*/
class Company
{
/**
* @var integer $companyid
*
* @ORM\Column(name="CompanyID", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $companyid;
/**
* @var \Model\Entity\LegalDetails $legaldetails
*
* @ORM\OneToOne(targetEntity="\Model\Entity\Legaldetails", mappedBy="companyid")
*/
private $legaldetails;
//other fields
public function __construct()
{
$this->legaldetails = new ArrayCollection();
}
//setters and getters
and legaldetails entity:
<?php
namespace Model\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
/**
* Legaldetails
*
* @ORM\Table(name="LegalDetails")
* @ORM\Entity
*/
class Legaldetails
{
/**
* @var integer $legalid
*
* @ORM\Column(name="LegalID", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $legalid;
/**
* @var \Model\Entity\Company $company
*
* @ORM\Column(name="CompanyID", type="integer", nullable=false)
* @ORM\OneToOne(targetEntity="\Model\Entity\Company", inversedBy="companyid")
* @ORM\JoinColumn(name="companyid", referencedColumnName="companyid")
*/
private $company;
What is wrong?
Best Answer
For those who came here with the question about "Expected end of string, got 'ON'", but could not find the right answer, as I couldn't (well, there is an answer, but not exactly about QueryBuilder). In general, yes, you don't need to specify the joining columns. But what if you need to add extra filtering. For example, I was looking to add an extra condition (to allow nulls in join).
The problem here is that even though the constant Join::ON exists (and comments in Join expression mention it as well), there is no ON in DQL. Instead, one should use WITH (Join::WITH).
Here is my usage example:
P.S. Predicting questions about
IFNULL()
- it is a Benjamin Eberlei's Doctrine extension.