Symfony 2 doctrine left join

Question

I have query

    $em = $this->getDoctrine()->getManager();
    $query = $em->createQueryBuilder();

    $query = $em->createQueryBuilder()
        ->select('com', 'cit')
        ->from('CatalogWebBundle:ComCompany', 'com')
        ->leftJoin('com.cmpCity', 'cit')
        ->getQuery()
        ->setMaxResults(1);

    $info = $query->getResult();

work fine, but I dont know how to get result from JOINED TABLE. I try use get method from JOINED table ENTITIES, but it not work ?

Maybe I need to ad something to ENTITIES ?

class ComCity
{
    /**
     * @var integer
     *
     * @ORM\Column(name="cit_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $citId;

    /**
     * @var string
     *
     * @ORM\Column(name="cit_name", type="string", length=255, nullable=true)
     */
    private $citName;



    /**
     * Get citId
     *
     * @return integer 
     */
    public function getCitId()
    {
        return $this->citId;
    }

    /**
     * Set citName
     *
     * @param string $citName
     * @return ComCity
     */
    public function setCitName($citName)
    {
        $this->citName = $citName;

        return $this;
    }

    /**
     * Get citName
     *
     * @return string 
     */
    public function getCitName()
    {
        return $this->citName;
    }
}

namespace Catalog\WebBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

class ComCompany
{
    /**
     * @var integer
     *
     * @ORM\Column(name="cmp_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $cmpId;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_name", type="string", length=255, nullable=true)
     */
    private $cmpName;

    /**
     * @var integer
     *
     * @ORM\Column(name="cmp_code", type="bigint", nullable=true)
     */
    private $cmpCode;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_vat", type="string", length=255, nullable=true)
     */
    private $cmpVat;

    /**
     * @var integer
     *
     * @ORM\Column(name="cmp_emp", type="integer", nullable=true)
     */
    private $cmpEmp;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_return", type="string", length=255, nullable=true)
     */
    private $cmpReturn;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_return_from", type="string", length=255, nullable=true)
     */
    private $cmpReturnFrom;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_return_till", type="string", length=255, nullable=true)
     */
    private $cmpReturnTill;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_address", type="string", length=255, nullable=true)
     */
    private $cmpAddress;

    /**
     * @var integer
     *
     * @ORM\Column(name="cmp_phone", type="bigint", nullable=true)
     */
    private $cmpPhone;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_email", type="string", length=255, nullable=true)
     */
    private $cmpEmail;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_site", type="string", length=255, nullable=true)
     */
    private $cmpSite;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_ceo", type="string", length=255, nullable=true)
     */
    private $cmpCeo;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_register", type="string", length=255, nullable=true)
     */
    private $cmpRegister;

    /**
     * @var string
     *
     * @ORM\Column(name="cmp_url", type="string", length=255, nullable=false)
     */
    private $cmpUrl;

    /**
     * @var \Catalog\WebBundle\Entity\ComCategory
     *
     * @ORM\ManyToOne(targetEntity="Catalog\WebBundle\Entity\ComCategory")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="cmp_category", referencedColumnName="cat_id")
     * })
     */
    private $cmpCategory;

    /**
     * @var \Catalog\WebBundle\Entity\ComCity
     *
     * @ORM\ManyToOne(targetEntity="Catalog\WebBundle\Entity\ComCity")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="cmp_city", referencedColumnName="cit_id")
     * })
     */
    private $cmpCity;



    /**
     * Get cmpId
     *
     * @return integer 
     */
    public function getCmpId()
    {
        return $this->cmpId;
    }

    /**
     * Set cmpName
     *
     * @param string $cmpName
     * @return ComCompany
     */
    public function setCmpName($cmpName)
    {
        $this->cmpName = $cmpName;

        return $this;
    }

    /**
     * Get cmpName
     *
     * @return string 
     */
    public function getCmpName()
    {
        return $this->cmpName;
    }

    /**
     * Set cmpCode
     *
     * @param integer $cmpCode
     * @return ComCompany
     */
    public function setCmpCode($cmpCode)
    {
        $this->cmpCode = $cmpCode;

        return $this;
    }

    /**
     * Get cmpCode
     *
     * @return integer 
     */
    public function getCmpCode()
    {
        return $this->cmpCode;
    }

    /**
     * Set cmpVat
     *
     * @param string $cmpVat
     * @return ComCompany
     */
    public function setCmpVat($cmpVat)
    {
        $this->cmpVat = $cmpVat;

        return $this;
    }

    /**
     * Get cmpVat
     *
     * @return string 
     */
    public function getCmpVat()
    {
        return $this->cmpVat;
    }

    /**
     * Set cmpEmp
     *
     * @param integer $cmpEmp
     * @return ComCompany
     */
    public function setCmpEmp($cmpEmp)
    {
        $this->cmpEmp = $cmpEmp;

        return $this;
    }

    /**
     * Get cmpEmp
     *
     * @return integer 
     */
    public function getCmpEmp()
    {
        return $this->cmpEmp;
    }

    /**
     * Set cmpReturn
     *
     * @param string $cmpReturn
     * @return ComCompany
     */
    public function setCmpReturn($cmpReturn)
    {
        $this->cmpReturn = $cmpReturn;

        return $this;
    }

    /**
     * Get cmpReturn
     *
     * @return string 
     */
    public function getCmpReturn()
    {
        return $this->cmpReturn;
    }

    /**
     * Set cmpReturnFrom
     *
     * @param string $cmpReturnFrom
     * @return ComCompany
     */
    public function setCmpReturnFrom($cmpReturnFrom)
    {
        $this->cmpReturnFrom = $cmpReturnFrom;

        return $this;
    }

    /**
     * Get cmpReturnFrom
     *
     * @return string 
     */
    public function getCmpReturnFrom()
    {
        return $this->cmpReturnFrom;
    }

    /**
     * Set cmpReturnTill
     *
     * @param string $cmpReturnTill
     * @return ComCompany
     */
    public function setCmpReturnTill($cmpReturnTill)
    {
        $this->cmpReturnTill = $cmpReturnTill;

        return $this;
    }

    /**
     * Get cmpReturnTill
     *
     * @return string 
     */
    public function getCmpReturnTill()
    {
        return $this->cmpReturnTill;
    }

    /**
     * Set cmpAddress
     *
     * @param string $cmpAddress
     * @return ComCompany
     */
    public function setCmpAddress($cmpAddress)
    {
        $this->cmpAddress = $cmpAddress;

        return $this;
    }

    /**
     * Get cmpAddress
     *
     * @return string 
     */
    public function getCmpAddress()
    {
        return $this->cmpAddress;
    }

    /**
     * Set cmpPhone
     *
     * @param integer $cmpPhone
     * @return ComCompany
     */
    public function setCmpPhone($cmpPhone)
    {
        $this->cmpPhone = $cmpPhone;

        return $this;
    }

    /**
     * Get cmpPhone
     *
     * @return integer 
     */
    public function getCmpPhone()
    {
        return $this->cmpPhone;
    }

    /**
     * Set cmpEmail
     *
     * @param string $cmpEmail
     * @return ComCompany
     */
    public function setCmpEmail($cmpEmail)
    {
        $this->cmpEmail = $cmpEmail;

        return $this;
    }

    /**
     * Get cmpEmail
     *
     * @return string 
     */
    public function getCmpEmail()
    {
        return $this->cmpEmail;
    }

    /**
     * Set cmpSite
     *
     * @param string $cmpSite
     * @return ComCompany
     */
    public function setCmpSite($cmpSite)
    {
        $this->cmpSite = $cmpSite;

        return $this;
    }

    /**
     * Get cmpSite
     *
     * @return string 
     */
    public function getCmpSite()
    {
        return $this->cmpSite;
    }

    /**
     * Set cmpCeo
     *
     * @param string $cmpCeo
     * @return ComCompany
     */
    public function setCmpCeo($cmpCeo)
    {
        $this->cmpCeo = $cmpCeo;

        return $this;
    }

    /**
     * Get cmpCeo
     *
     * @return string 
     */
    public function getCmpCeo()
    {
        return $this->cmpCeo;
    }

    /**
     * Set cmpRegister
     *
     * @param string $cmpRegister
     * @return ComCompany
     */
    public function setCmpRegister($cmpRegister)
    {
        $this->cmpRegister = $cmpRegister;

        return $this;
    }

    /**
     * Get cmpRegister
     *
     * @return string 
     */
    public function getCmpRegister()
    {
        return $this->cmpRegister;
    }

    /**
     * Set cmpUrl
     *
     * @param string $cmpUrl
     * @return ComCompany
     */
    public function setCmpUrl($cmpUrl)
    {
        $this->cmpUrl = $cmpUrl;

        return $this;
    }

    /**
     * Get cmpUrl
     *
     * @return string 
     */
    public function getCmpUrl()
    {
        return $this->cmpUrl;
    }

    /**
     * Set cmpCategory
     *
     * @param \Catalog\WebBundle\Entity\ComCategory $cmpCategory
     * @return ComCompany
     */
    public function setCmpCategory(\Catalog\WebBundle\Entity\ComCategory $cmpCategory = null)
    {
        $this->cmpCategory = $cmpCategory;

        return $this;
    }

    /**
     * Get cmpCategory
     *
     * @return \Catalog\WebBundle\Entity\ComCategory 
     */
    public function getCmpCategory()
    {
        return $this->cmpCategory;
    }

    /**
     * Set cmpCity
     *
     * @param \Catalog\WebBundle\Entity\ComCity $cmpCity
     * @return ComCompany
     */
    public function setCmpCity(\Catalog\WebBundle\Entity\ComCity $cmpCity = null)
    {
        $this->cmpCity = $cmpCity;

        return $this;
    }

    /**
     * Get cmpCity
     *
     * @return \Catalog\WebBundle\Entity\ComCity 
     */
    public function getCmpCity()
    {
        return $this->cmpCity;
    }
}

Answer 1

getResult() returns the entire set of results as an iterable array collection, not a single result. Thus, when you are using setMaxResults(1), it is wise to fetch that single result using getSingleResult():

$info = $query->getSingleResult();

If no results are returned, $info will be null. Otherwise you will be able to access the cmpCity property using:

$cmpCity = $info->getCmpCity();

Again, $cmpCity will be null if there is no cmpCity associated with the ComCompany object. If you want to get the first ComCompany object that does have an associated cmpCity, use innerJoin instead of leftJoin:

$query = $em->createQueryBuilder()
    ->select('com', 'cit')
    ->from('CatalogWebBundle:ComCompany', 'com')
    ->innerJoin('com.cmpCity', 'cit')
    ->getQuery()
    ->setMaxResults(1);

$info = $query->getSingleResult();
$cmpCity = $info->getCmpCity();

Answer 2

This might be related to the issue when you combine leftJoin and setMaxResults.

There is already answers on how you should do your query : https://stackoverflow.com/a/14886847/3726645