INNER JOIN Query + WHERE doesn't work

Question

I have two tables: contactperson and contactpersonlocale.

Table contactperson:

• contactpersonID (Primary Key)
• tag (VARCHAR)

Table contactpersonlocale:

• contactpersonlocaleID (Primary Key)
• contactpersonID (Foreign Key to contactperson table)
• function (VARCHAR)
• name (VARCHAR)
• locale (VARCHAR)

In my Contactperson Entity I have:

/**
 * @var integer
 *
 * @ORM\Column(name="contactpersonID", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $contactpersonid;

/**
 * @ORM\OneToMany(targetEntity="DX\MurisBundle\Entity\Contactpersonlocale", mappedBy="contactpersonid", cascade={"persist", "remove", "merge"}, orphanRemoval=true)
 */
protected $contactpersonlocale;

/**
 * Set contactpersonlocale
 *
 * @param \DX\MurisBundle\Entity\Contactpersonlocale $contactpersonlocale
 * @return Contactpersonlocale
 */
public function setContactpersonlocale(\DX\MurisBundle\Entity\Contactpersonlocale $contactpersonlocale = null)
{
    $this->contactpersonlocale = $contactpersonlocale;

    return $this;
}

/**
 * Get contactpersonlocale
 *
 * @return \DX\MurisBundle\Entity\Contactpersonlocale
 */
public function getContactpersonlocale()
{
    return $this->contactpersonlocale;
}

In my Contactpersonlocale Entity I have:

/**
 * @var \DX\MurisBundle\Entity\Contactperson
 *
 * @ORM\ManyToOne(targetEntity="DX\MurisBundle\Entity\Contactperson")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="contactpersonID", referencedColumnName="contactpersonID")
 * })
 */
private $contactpersonid;

In my Contactperson Repository I have:

public function getContactpersonen($locale = 'nl')
{
    $cp = $this->createQueryBuilder('cp')
        ->select('cp')
        ->innerJoin('cp.contactpersonlocale', 'cpl')
        ->where('cpl.locale = :locale')
        ->setParameter('locale', $locale);

    return $cp->getQuery()
        ->getResult();
}

Now when I loop through them like this:

$contactpersonen = $em->getRepository('MurisBundle:Contactperson')->getContactpersonen($locale);
foreach($contactpersonen as $cp)
{
    dump($cp->getcontactpersonlocale()->toArray()); die;
}

Then I get two personlocale objects, he doesn't take the locale in account (as you can see I do WHERE locale = ..). And my locale is definitely filled in .. .

What could be the problem of this?

Answer 1

You have to use the WITH statement in your query:

$cp = $this->createQueryBuilder('cp')
        ->select('cp')
        ->innerJoin('cp.contactpersonlocale', 'cpl', 'WITH', 'cpl.locale = :locale')
        ->setParameter('locale', $locale);

If you want to join only Contactpersonlocale with locale = $locale.