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

(Sorry for my incoherent question: I tried to answer some questions as I was writing this post, but here it is:)

I'm trying to create a database model with a many-to-many relationship inside a link table, but which also has a value per link, in this case a stock-keeping table. (this is a basic example for more problems I'm having, but I thought I'd just test it with this before I would continue).

I've used exportmwb to generate the two Entities Store and Product for this simple example, both are displayed below.

However, the problem now is that I can't figure out how to access the stock.amount value (signed int, as it can be negative) using Doctrine. Also, when I try to create the tables using doctrine's orm:schema-tool:create function

This yielded only two Entities and three tables, one as a link table without values and two data tables, as many-to-many relationships aren't entities themselves so I can only have Product and Store as an entity.

So, logically, I tried changing my database model to have stock as a separate table with relationships to store and product. I also rewrote the fieldnames just to be able to exclude that as a source of the problem:

Then what I found was that I still didn't get a Stock entity... and the database itself didn't have an 'amount'-field.

I really needed to be able to bind these stores and products together in a stock table (among other things)... so just adding the stock on the product itself isn't an option.

root@hdev:/var/www/test/library# php doctrine.php orm:info
Found 2 mapped entities:
[OK]   Entity\Product
[OK]   Entity\Store

And when I create the database, it still doesn't give me the right fields in the stock table:

So, looking up some things here, I found out that many-to-many connections aren't entities and thus cannot have values. So I tried changing it to a separate table with relationships to the others, but it still didn't work.

What am I doing wrong here?

Solution 1:

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;
}

Solution 2:

Doctrine handles many-to-many relationships just fine.

The problem that you're having is that you don't need a simple ManyToMany association, because associations can't have "extra" data.

Your middle (stock) table, since it contains more than product_id and store_id, needs its own entity to model that extra data.

So you really want three classes of entity:

• Product
• StockLevel
• Store

and two associations:

• Product oneToMany StockLevel
• Store oneToMany StockLevel