92

(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).

Database model for a basic multi-store, multi-product store-keeping system

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

the database layout as it is seen from HeidiSQL

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:

changed database layout

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:

the database layout as it is seen from HeidiSQL

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?

Henry van Megen
  • 2,159
  • 2
  • 23
  • 35
  • Ok, I found a couple of mentions stating that it's not possible to have many-to-many connections using Doctrine, with comments advising to prevent these relationships.. but what if you are really stuck with a situation such as the one I described in my original question? I have an entire database, compatible with Magento, that completely relies on many-to-many relationships. So basically I'm being told "Doctrine ORM can't handle many-to-many, don't use it" ?? – Henry van Megen Mar 25 '13 at 15:13
  • See also [Doctrine2: Best way to handle many-to-many with extra columns in reference table](http://stackoverflow.com/questions/3542243/doctrine2-best-way-to-handle-many-to-many-with-extra-columns-in-reference-table) – Onshop Oct 03 '13 at 13:45
  • 3
    Would give you +100 if I could for the effort you have made to explain exactly what I was wondering about in such a nice way :-) – Torsten Römer Mar 21 '14 at 20:06

2 Answers2

152

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;
}
Ocramius
  • 25,171
  • 7
  • 103
  • 107
  • Ok, I'll add some getter and setters, because with this setup, I only get the primary keys up and running without any values :) – Henry van Megen Mar 27 '13 at 08:34
  • When I use this setup and then try to query using Stock.store_id, I get the error "Stock has no field or association named store_id". It should be found because the column exists in the database. – afilina Aug 06 '13 at 13:23
  • @afilina the db doesn't matter while generating the schema - the DBAL throws the exception because it doesn't find the column in the DDL metadata (in memory) – Ocramius Aug 08 '13 at 08:41
  • @Ocramius What I meant was that the DB was generated from metadata. SIf it was able to generate the column in the first place, then it should be able to find it during the query. The solution to my problem was to compare Stock.store to the desired id. – afilina Aug 08 '13 at 14:41
  • 100% what I need and it works like a charm! Do you know how to build a form with fieldset in order to edit the amount per store and product ? – cwhisperer Nov 28 '14 at 08:24
  • That's a different question. – Ocramius Nov 29 '14 at 12:56
  • thanks for this Answer, its working perfect, only thing i do struggle with is to remove a specific Stock-Entity because i doesnt have a unique identifier – john Smith Dec 03 '14 at 14:14
  • What would the form look like for this type of setup? – user1029829 Dec 05 '16 at 01:38
  • @user1029829 most probably manual setup, no auto-magic binding. – Ocramius Dec 05 '16 at 22:46
  • I know this is very old, but would you recommend the same setup if the stock table did not always have an amount? Obviously that isn't realistic in this scenario but I have a similar situation where the "pivot table" doesn't always have the extra data. – Dylan Buth Apr 19 '17 at 18:20
  • Tks! But, where i put an ArrayCollection for control this? – Szag-Ot May 26 '17 at 12:34
  • @Szag-Ot in a private property? – Ocramius May 27 '17 at 18:25
  • @Ocramius https://pt.stackoverflow.com/questions/207760/doctrine-relacionamento-manytomany-com-campos-extras – Szag-Ot May 27 '17 at 20:23
  • No idea what to do with a question in Portuguese, sorry. – Ocramius May 29 '17 at 00:06
17

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
timdev
  • 61,857
  • 6
  • 82
  • 92
  • 1
    Thank you for your answer ! I added extra fields to my "stock" like table. However, doctrine still not consider this "join table" and skip it when I run `php app/console doctrine:mapping:import AppBundle yml` in order to import the schema from the database. I would like it to generate this extra mapping yaml file. Does anyone have any Idea ? `:(` – Stphane Mar 16 '15 at 16:22
  • Answer not resolving writing data to "connection" entity. This is a problem. Not declaring entities. Can please someone support example where data are passed from Form (CollectionType field has embedded form with EntityType field) . I can't pass data from embeded form to main form, and save collection of fields properly – zoore Feb 22 '18 at 21:09