Doctrine 2 JOIN error

Question

I try to execute this query

    $qb = $this->_em->createQueryBuilder();
    $qb->select(array('c', 'ld'))
            ->from('Model\Entity\Company', 'c')
            ->leftJoin('c.legaldetails', 'ld')
            ->where("c.companyid = 1");

    $query = $qb->getQuery();
    echo($query->getSQL());

having this sql code at the end:

SELECT ... FROM Company c0_ LEFT JOIN WHERE c0_.CompanyID = 1

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?

P.S.: I understand that having two fields with identical names (companyid) is a bad practice, but it is not my fault

Gangnus · Accepted Answer · 2012-01-20T08:28:08.423

2

Judging on SQL operator, JOIN ON what? You missed the key part of join operator. Maybe, ON table.companyid=table2.companyid? Using the same names in tables could be even useful and is usual, not bad practice. You could put here the full SQL operator, that would be a better practice. :-)

edited Jan 20 '12 at 08:28

answered Jan 20 '12 at 08:21

Gangnus

24,044
16
90
149

I don't understand you :-/ I know SQL syntax. But this SQL-code was generated by Doctrine's query builder. May be you say about table prefix in my entity? I did try to do this: @ORM\OneToOne(targetEntity="\Model\Entity\Company", inversedBy="company.companyid") @ORM\JoinColumn(name="legaldetails.companyid", referencedColumnName="company.companyid") ... @ORM\OneToOne(targetEntity="\Model\Entity\Legaldetails", mappedBy="legaldetails.companyid") It didn't help – Russian Raccoon Jan 20 '12 at 08:49
If I am to find a problem, excuse me, I find it where I see it. This time I see it in the SQL code. 1. please, put it *all* there. 2. I see JOIN operator without ON clause. How could it work? – Gangnus Jan 20 '12 at 09:02
When I try do this `->innerJoin('c.legaldetails', 'ld', 'ON', 'c.companyid=ld.companyid')` I have error `Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 70: Error: Expected end of string, got 'ON'` – Russian Raccoon Jan 20 '12 at 09:05
So, we have found the error here. Now let's solve another question. Put it as a new question, please and put a reference here for me – Gangnus Jan 20 '12 at 09:08
You are welcome. Put, please, that another problem as a question - I have found a solution, I think. – Gangnus Jan 20 '12 at 09:17
I ask new question here [http://stackoverflow.com/questions/8939148/doctrine-2-join-on-error] – Russian Raccoon Jan 20 '12 at 09:25

Doctrine 2 JOIN error

1 Answers1