Doctrine 2 JOIN ON error

doctrine-ormdqljoin;

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:

$qb->leftJoin('p.metadata', 'm', Join::WITH, "IFNULL(m.name, '') = 'email'");

P.S. Predicting questions about IFNULL() - it is a Benjamin Eberlei's Doctrine extension.

Related Topic