Working example of joining two tables with join table using Doctrine 2

Question

Doctrine has got some nice documentation, but at some point I have the feeling for someone who wants to get in to doctrine it is sort of a small battle to get used to the mapping stuff. I am one of those guys who belongs to this section. I have gone through most of the mapping stuff documentation for example like this and other links in doctrine official site, but the documentation for me looks like bits and pieces to follow. I am saying this for my case.

Is there somewhere an example which shows how can I join two tables with a third join table, I wanted to know the basic mapping for this schema.

Let me say I have two tables: Fruits and Country.

The relationship is that one country produces many varieties of fruits, so as to say that is a onetomany and manytoone relationship. Apart from that I wanted to do association using a third table say countryFruits.

Fruits Table
-- fruitsId (PK, AI)
-- fruitName

Country Table
-- countryId (PK, AI)
-- countryName

countryFruits Table
-- fruitsId (PK, FK)
-- countryId (PK, FK)

That is how the tables in MySQL look like and it has already been designed. Now I can fill the fruits table with doctrine and when it comes to filling the country table, I get a messed up mapping problem.

/**
 * @ORM\Entity
 * @ORM\Table(name="fruits")
 * @property string $fruitName
 * @property int $fruitId
 */
class Fruits
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", name="fruitId", unique=true);
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $fruitId;

    /**
     * @ORM\Column(type="string")
     */
    protected $fruitName;

    /**
     * @ORM\OneToMany(targetEntity="Country", mappedBy="fruits", cascade={"persist"})
     */
    protected $country;

    public function __get($property)
    {
        return $this->$property;
    }

    public function __set($property, $value)
    {
        $this->$property = $value;
    }

}

/**
 * @ORM\Entity
 * @ORM\Table(name="country")
 * @property string $countryName
 * @property int $countryId
 */
class Country
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", name="countryId", unique=true);
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $countryId;

    /**
     * @ORM\Column(type="string")
     */
    protected $countryName;

    /**
     * @ORM\OneToMany(targetEntity="Fruits", mappedBy="country", cascade={"persist"})
     */
    protected $countries;

    public function __get($property)
    {
        return $this->$property;
    }

    public function __set($property, $value)
    {
        $this->$property = $value;
    }

}

/**     
 * @ORM\Entity
 * @ORM\Table(name="countryFruits ")
 * @property int $fruitId
 * @property int $countryId
 */
class countryFruits
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", name="fruitId", nullable=false)
     * @ORM\GeneratedValue(strategy="NONE")
     */
    protected $fruitId;

    /**
     * @ORM\Id
     * @ORM\Column(type="integer", name="countryId", nullable=false)
     * @ORM\GeneratedValue(strategy="NONE")
     */
    protected $countryId;

    /**
     * @ORM\ManyToOne(targetEntity="Country", inversedBy="fruits", cascade={"persist"})
     * @ORM\JoinColumn(name="countryId", referencedColumnName="countryId")
     */
    protected $country;

    /**
     * @ORM\ManyToOne(targetEntity="Fruits", inversedBy="country", cascade={"persist"})
     * @ORM\JoinColumn(name="fruitId", referencedColumnName="fruitId")
     */
    protected $fruits;

    /**
     * Set fruits
     *
     * @param Fruits $fruits
     */
    public function setFruits($fruits)
    {
        $this->fruits = $fruits;
    }

    /**
     * ´Get fruits
     *
     * @param Fruits $fruits
     */
    public function getFruits()
    {
        return $this->fruits;
    }

    /**
     * Set country
     *
     * @param Country $country
     */
    public function setCountry($country)
    {
        $this->country = $country;
    }

    /**
     * Get country
     *
     * @param Country $country
     */
    public function getCountry($country)
    {
        $this->country = $country;
    }
}

Can somebody cross check this and let me know if my mappings are done in the proper way. In case, is it possible to get a small piece of code how to persist the entities into my database.

Answer 1

you don't need the countryFruits class. what you're searching for is the ManyToMany relation! also you don't want to name entities in plural, as an entity always represends a single object/row in the table.

Fruit entity

/**
 * @ORM\Entity
 * @ORM\Table(name="fruits")
 * @property string $fruitName
 * @property int $fruitId
 */
class Fruit
{
    /**
      * @ORM\ManyToMany(targetEntity="Country")
      * @ORM\JoinTable(name="country_fruits",
      *         joinColumns={@ORM\JoinColumn(name="country_id", referencedColumnName="id")},
      *         inverseJoinColumns={@ORM\JoinColumn(name="fruit_id", referencedColumnName="id")}
      * ) 
      * @var Country[]
      */
      protected $countries;
}

Country entity

/**
 * @ORM\Entity
 * @ORM\Table(name="country")
 * @property string $countryName
 * @property int $countryId
 */
class Country
{
    /**
      * @ORM\ManyToMany(targetEntity="Fruit")
      * @ORM\JoinTable(name="country_fruits",
      *         joinColumns={@ORM\JoinColumn(name="fruit_id", referencedColumnName="id")},
      *         inverseJoinColumns={@ORM\JoinColumn(name="country_id", referencedColumnName="id")}
      * ) 
      * @var Fruit[]
      */
      protected $fruits;
 }

note that you don't need the countryFruits class, but the table country_fruits is required!