paolo@bimodesign.com | +34 608 61 64 10

Framework

        

Doctrine - Crud Many to Many

There are several ways to select data from three (or more) table that have a relation between them. In this post I'll explain the createQuery solution
First of all we'll have this database schema's

doctrine crud many to many

We have the table Country, the table Languages and the relational table country_language where we'll make the connection between a country and it own language(s).
This is the steps
1. Generate the entity, using these commands:

./vendor/doctrine/doctrine-module/bin/doctrine-module orm:convert-mapping --namespace="Application\\Entity\\" --force  --from-database annotation ./module/Application/src/
./vendor/doctrine/doctrine-module/bin/doctrine-module orm:generate-entities ./module/Application/src/ --regenerate-entities=true --generate-annotations=true
Those commands generate only two entities:
- Country.php
- Language.php

cause the relations between these to table/entity will be into the Country.php:

    namespace Application\Entity;

    use Doctrine\ORM\Mapping as ORM;

    /**
     * Country
     *
     * @ORM\Table(name="country")
     * @ORM\Entity
     */
    class Country
    { 

    .... code ...
    .... code ...
    
    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Application\Entity\Language", inversedBy="coun")
     * @ORM\JoinTable(name="country_language",
     *   joinColumns={
     *     @ORM\JoinColumn(name="coun_id", referencedColumnName="coun_id", nullable=true)
     *   },
     *   inverseJoinColumns={
     *     @ORM\JoinColumn(name="lang_id", referencedColumnName="lang_id", nullable=true)
     *   }
     * )
     */
    private $lang;

    .... code ...
    .... code ...

    /**
     * Add lang
     *
     * @param \Application\Entity\Language $lang
     * @return Country
     */
    public function addLang(\Application\Entity\Language $lang)
    {
        $this->lang[] = $lang;

        return $this;
    }

    /**
     * Remove lang
     *
     * @param \Application\Entity\Language $lang
     */
    public function removeLang(\Application\Entity\Language $lang)
    {
        $this->lang->removeElement($lang);
    }

    /**
     * Get lang
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getLang()
    {
        return $this->lang;
    }
}

We can notice the definition of the propriety lang and the methods to access to it.
addLang: that accept a input type language
and add a new record into the table country_language removeLang: that accept a input type language
and remove a record from the table country_languages getLang: that return all information about the language. From de language table

Well... we suppose that in my form I need to show all the languages (active) linked with a country and show the language that isn't to add to the country. And, of course, I would to remove one or more language from that country.
The form will be like it

doctrine crud many to many

So these is the methods that I put into the my Country class that extends the country entity.
Select the language' country
From the controller I call the findOneBy doctrine methods

'country' => $this->Country()->findOneBy(array('counId' => $this->params()->fromRoute('id'))),


Select the list of the languages associated to that country and that is active
In this case I call the method findLanguageCountry where that have the CountryId and the value of the the active flag.

'listLanguageActiveByCountry' => $this->Country()->findLanguageCountry($this->params()->fromRoute('id'), $active),

This method has the code

    public function findLanguageCountry($counId, $langActive) {
        $query = $this->em->createQuery('select language.langId, language.langName '
                                    . ' from Application\Entity\Language language'
                                    . ' where language.langId NOT IN (select languages.langId from Application\Entity\Country country '
                                    . '                                 left join country.lang languages'
                                    . '                                where country.counId = '.$counId.')'
                                    . '  and language.langActive = '.$langActive.'');         
        
        return $query->getResult();
    }  

It's interesting to notice this part of the code/select

    left join country.lang languages'

The Join between country and country_language, to select the list of country's languages, is by the parameter and not with the table.
Note: We need to rename the language table into the subselect cause we are already declaring this table above.
The last two methods are:

Delete one language from the country
I'll explain in another post how to manage the confirm popup to delete a record (in Zend 2). Here I'll post only the simple code to delete the record from the table country_language.

    $this->Country()->removeLanguage($countryId, $languageId);

and into the method we have

    public function removeLanguage($countryId, $languageId) {
        $language = new \Application\Model\DB\Language($this->pm);

        if ($country = $this->find($countryId)) {
            $language = $language->find($languageId);

            $country->removeLang($language);
            $this->em->flush();
            return true;
        }
    }    

We need to get the data about the country and so the data about the language. Do you remember which kind the input type accept the removeLang function?). So we call this method and... nothing more!

Finally,
Add one or more language to the country
In this case I'll call the

$this->Country()->addLanguage($countryId, $value);

for all the languages I need to associated to country.
The addLanguage method will have this code

    public function addLanguage($countryId, $languageId) {
        $language = new \Application\Model\DB\Language($this->pm);

        if ($country = $this->find($countryId)) {
            $language = $language->find($languageId);

            $country->addLang($language);
            $this->em->flush();
            return true;
        }
    }    

It's similar to removeLanguage.