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?

Connel

The query correctlty extract all contractperson that have at least one locale of your... – Sena

And how can I change this? – Connel

What are your need? What do you want to extract from the db? – Sena

In my contactpersonLocale I have 2x the same conctactpersonID, but they both have a different locale. And now I would like to select all the contactpersons where locale = "nl". – Connel

I suggest you to select all the contractPersonLocale entity that suddisfy your criteria the navigate for the contractPersonentity data with and inversedByrelation. Let me know if this approach can be done in your case. Hope this help – Sena

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.

Ansley

Still giving me 2 contactpersonlocales .. He doesn't take the locale into account... – Connel

@Connel it's working here, what query are you using? What does the profiler tell you? – Ansley