Doctrine2: Best Way to Handle Many-to-many With Extra Columns in Reference Table

Doctrine2: Best way to handle many-to-many with extra columns in reference table

I've opened a similar question in the Doctrine user mailing list and got a really simple answer;

consider the many to many relation as an entity itself, and then you realize you have 3 objects, linked between them with a one-to-many and many-to-one relation.

http://groups.google.com/group/doctrine-user/browse_thread/thread/d1d87c96052e76f7/436b896e83c10868#436b896e83c10868

Once a relation has data, it's no more a relation !

Laravel doctrine2 many to many relation with extra column

As per my solution which has worked, by reading another question passed by @Nicola Havric - Read as follow That doctrine does not support extra columns in a many-to-many relation. Thus you should use the relation as it's own entity. My own solution was to change the way I wanted it to run with flushing.

In my controller I changed my code as follow:

try {
    $profile = new Profile(
        $request->input('company_id'),
        $request->input('name'),
        $request->input('lastname'),
        $request->input('gender'),
        new DateTime(),
        $time,
        $time
    );

    $company = $this->em->getRepository(Company::class)->find($request->input('company_id'));
    $profile->addCompany($company);
    //Flush the user, so I can grab it's profile ID
    $this->em->persist($profile);
    $this->em->flush();

    foreach($request->input('profile_skills') as $skill => $level) {
        $skill = $this->em->getRepository(Skill::class)->find($skill);
        $skill->level = $level;
        $profile->addSkill($skill);
    }


    $this->em->getConnection()->commit();

Inside my Profile Entity function:

public function addSkill(Skill $skill)
{
    //I left this check since it only checks if the relation is set already. If so, it will skip it.
    if ($this->skills->contains($skill)) {
        return;
    }

    //Since this function gets called inside a loop, I can call the entity to add a new "relation" to the table.
    (new ProfileHasSkill($this->getId(), $skill, $skill->level))->addSkill($this->getId(), $skill, $skill->level);

    return true;
}

Inside my ProfileHasSkill entity:

 public function addSkill($profileId, $skill)
{
    //Creating a new ProfileHasSkill inside the table.
    $profileSkill = new ProfileHasSkill(
        $profileId,
        $skill->getId(),
        $skill->level
    );

    /*Since I do a roll-back inside my controller in case something goes wrong.
       I decided to add the flush here. 
       As far no additional checks where needed in my case 
       since I require a Profile instance and a Skill instance inside the Profile entity.*/
    EntityManager::persist($profileSkill);
    EntityManager::flush();
}

Doctrine 2 and Many-to-many link table with an extra field

A Many-To-Many association with additional values is not a Many-To-Many, but is indeed a new entity, since it now has an identifier (the two relations to the connected entities) and values.

That's also the reason why Many-To-Many associations are so rare: you tend to store additional properties in them, such as sorting, amount, etc.

What you probably need is something like following (I made both relations bidirectional, consider making at least one of them uni-directional):

Product:

namespace Entity;

use Doctrine\ORM\Mapping as ORM;

/** @ORM\Table(name="product") @ORM\Entity() */
class Product
{
    /** @ORM\Id() @ORM\Column(type="integer") */
    protected $id;

    /** ORM\Column(name="product_name", type="string", length=50, nullable=false) */
    protected $name;

    /** @ORM\OneToMany(targetEntity="Entity\Stock", mappedBy="product") */
    protected $stockProducts;
}

Store:

namespace Entity;

use Doctrine\ORM\Mapping as ORM;

/** @ORM\Table(name="store") @ORM\Entity() */
class Store
{
    /** @ORM\Id() @ORM\Column(type="integer") */
    protected $id;

    /** ORM\Column(name="store_name", type="string", length=50, nullable=false) */
    protected $name;

    /** @ORM\OneToMany(targetEntity="Entity\Stock", mappedBy="store") */
    protected $stockProducts;
}

Stock:

namespace Entity;

use Doctrine\ORM\Mapping as ORM;

/** @ORM\Table(name="stock") @ORM\Entity() */
class Stock
{
    /** ORM\Column(type="integer") */
    protected $amount;

    /** 
     * @ORM\Id()
     * @ORM\ManyToOne(targetEntity="Entity\Store", inversedBy="stockProducts") 
     * @ORM\JoinColumn(name="store_id", referencedColumnName="id", nullable=false) 
     */
    protected $store;

    /** 
     * @ORM\Id()
     * @ORM\ManyToOne(targetEntity="Entity\Product", inversedBy="stockProducts") 
     * @ORM\JoinColumn(name="product_id", referencedColumnName="id", nullable=false) 
     */
    protected $product;
}

How to implement many-to-many with extra fields in symfony2?

Once an association has data, it's no more an association.

You have to implement two ManyToOne instead of a ManyToMany.

See this great answer on this question for a full example.

You can get a lot of other examples by googling the title of your question.

Doctrine 2: How to handle join tables with extra columns

First off, let me explain that this does not exist:

A join table (also known as a junction table or cross-reference table) is a table that links 2 (or more) other tables together within the same database by primary key.
This means that a join table will only contain foreign keys, there is no place for these extra columns.

So when you need extra columns in such a table, it is no longer just a "link" between other tables, but becomes a real table on its own!

In terms of Doctrine 2, you no longer have a many-to-many association between 2 entities, but get a one-to-many/many-to-one association between 3 entities.

Continue reading here for more detailed explanations:

• Doctrine 2: How to handle join tables with extra columns
• More on one-to-many/many-to-one associations in Doctrine 2

How to create many-to-many self-referencing association with extra fields by using Doctrine 2?

You need to create a separate entity to link your Entities. Something like ProductCompound.

And then link it twice to your Product entity, for each relation.

/**
 * Class ProductCompound
 *
 * @ORM\Entity
 * @ORM\Table(name="compound_products")
 */
class ProductCompound
{
    /**
     * @ORM\id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     *
     * @var int
     */
    protected $id;

    /**
     * @ORM\ManyToOne(
     *     targetEntity="Product",
     *     inversedBy="products"
     * )
     * @ORM\JoinColumn(name="main_product_id", referencedColumnName="id"
     *
     * @var ArrayCollection
     */
    protected $mainProduct;

    /**
     * @ORM\ManyToOne(
     *     targetEntity="Product",
     *     inversedBy="components"
     * )
     * @ORM\JoinColumn(name="component_product_id", referencedColumnName="id"
     *
     * @var ArrayCollection
     */
    protected $componentProduct;

    /**
     * @var double
     *
     * @ORM\Column(name="amount", type="float", nullable=true)
     */
    protected $amount;

And modify Product:

    /**
     * Class Product
     *
     * @ORM\Entity
     * @ORM\Table(name="products")
     */
    class Product
    {
...

    /**
     * @ORM\OneToMany(
     *     targetEntity="ProductCompound",
     *     mappedBy="mainProduct"
     * )
     *
     * @var ArrayCollection
     */
    private $productLinks;

    /**
     * @ORM\OneToMany(
     *     targetEntity="ProductCompound",
     *     mappedBy="componentProduct"
     * )
     *
     * @var ArrayCollection
     */
    private $componentLinks;

---

Related Topics