INNER JOIN Query + WHERE not working

Question

I have two tables: contactperson and contactpersonlocale .

Contactperson table:

• contactpersonID (primary key) tag
• (VARCHAR)

Contactpersonlocale table:

• contactpersonlocaleID (primary key)
• contactpersonID (foreign key to the table of contact persons)
• (VARCHAR)
• name (VARCHAR)
• locale (VARCHAR)

In my Contactperson organization, I:

/** * @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 object, 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 scroll through them like this:

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

Then I get two object objects, it does not accept the locale account (as you can see, I do WHERE locale = ..). And my language is definitely full ...

What could be the problem?

Answer 1

You should 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.