Doctrine 2 Many to Many relation with additional columns in join table

Question

So, I have been playing round with using doctrine for a while now and have it in some basic projects, but i decided to go back and have an in depth look into what it can do.

Ive now decided to switch to symfony 2 as my framework of choice and am looking into what doctrine 2 can do in more depth.

One thing i have been trying to get my head around is the many to many relationship within doctrine. I am starting to build a recipe system and am working on the relation between recipe and ingredients which gave me 3 entities, recipe, recipeIngredient and ingredient. The reason i cannot use a direct many to many relation is because i want to store two additional columns in the join table ( unit and quantity ) for each ingredient.

The problem i am having at the moment is that the entities persist ok, but the recipe_id in the join table is not inserted. I have tried everything i can think off and been through every thread and website looking for an answer . I am sure it is something completely obvious that i am missing. Please help, below is the code i have so far:

<?php
namespace Recipe\RecipeBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
/**
 * @ORM\Entity
 * @ORM\Table(name="recipe")
 * @ORM\HasLifecycleCallbacks()
 */
class Recipe{

/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\OneToMany(targetEntity="RecipeIngredient", mappedBy="recipe", cascade=       {"persist"})
 */
protected $ingredients;
/**
 * @ORM\Column(type="string")
 * @var string $title
 *
 */
protected $title;
/**
 * Constructor
 */
public function __construct()
{
    $this->ingredients = new \Doctrine\Common\Collections\ArrayCollection();
}

/**
 * Get id
 *
 * @return integer
 */
public function getId()
{
    return $this->id;
}

/**
 * Add ingredients
 *
 * @param \Recipe\RecipeBundle\Entity\RecipeIngredient $ingredients
 * @return Recipe
 */
public function addIngredient(\Recipe\RecipeBundle\Entity\RecipeIngredient $ingredients)
{
    $ingredients->setRecipe($this);
    $this->ingredients[] = $ingredients;

    return $this;
}

/**
 * Remove ingredients
 *
 * @param \Recipe\RecipeBundle\Entity\RecipeIngredient $ingredients
 */
public function removeIngredient(\Recipe\RecipeBundle\Entity\RecipeIngredient $ingredients)
{
    $this->ingredients->removeElement($ingredients);
}

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

/**
 * Set title
 *
 * @param string $title
 * @return Recipe
 */
public function setTitle($title)
{
    $this->title = $title;

    return $this;
}

/**
 * Get title
 *
 * @return string
 */
public function getTitle()
{
    return $this->title;
}
}

and recipeIngredient

/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\ManyToOne(targetEntity="Recipe", inversedBy="ingredients")
 * */
protected $recipe;

/**
 * @ORM\ManyToOne(targetEntity="Ingredient", inversedBy="ingredients" , cascade={"persist"})
 * */
protected $ingredient;

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

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

/**
 * Get id
 *
 * @return integer
 */
public function getId()
{
    return $this->id;
}

/**
 * Set quantity
 *
 * @param string $quantity
 * @return RecipeIngredient
 */
public function setQuantity($quantity)
{
    $this->quantity = $quantity;

    return $this;
}

/**
 * Get quantity
 *
 * @return string
 */
public function getQuantity()
{
    return $this->quantity;
}

/**
 * Set unit
 *
 * @param string $unit
 * @return RecipeIngredient
 */
public function setUnit($unit)
{
    $this->unit = $unit;

    return $this;
}

/**
 * Get unit
 *
 * @return string
 */
public function getUnit()
{
    return $this->unit;
}

/**
 * Set recipe
 *
 * @param \Recipe\RecipeBundle\Entity\Recipe $recipe
 * @return RecipeIngredient
 */
public function setRecipe(\Recipe\RecipeBundle\Entity\Recipe $recipe = null)
{
    $this->recipe = $recipe;

    return $this;
}

/**
 * Get recipe
 *
 * @return \Recipe\RecipeBundle\Entity\Recipe
 */
public function getRecipe()
{
    return $this->recipe;
}

/**
 * Set ingredient
 *
 * @param \Recipe\RecipeBundle\Entity\Ingredient $ingredient
 * @return RecipeIngredient
 */
public function setIngredient(\Recipe\RecipeBundle\Entity\Ingredient $ingredient = null)
{
    $this->ingredient = $ingredient;

    return $this;
}

/**
 * Get ingredient
 *
 * @return \Recipe\RecipeBundle\Entity\Ingredient
 */
public function getIngredient()
{
    return $this->ingredient;
}
}

Yes, this code seems to be fine .... As Gremo says, please show us more details — DonCallisto, Nov 22 '12 at 10:11
Best guess from this is you're not explicitly setting the Recipe instance into the RecipeIngredient instance, and as RI is the owning side the relationship isn't persisted to the database. — jstephenson, Nov 22 '12 at 16:08

score 1 · Answer 1 · edited May 23 '17 at 12:17

Your basic idea is the correct one. If you want to have a ManyToMany relation, but you need to add extra fields in the join table, the way to go is exactly as you have described: using a new entity having 2 ManyToOne relations and some additional fields.

Unfortunately you have not provided your controller code, because most likely your problem is there.

Basically if you do something like:

$ri = new RecipeIngredient;
$ri->setIngredient($i);
$ri->setRecipe($r);
$ri->setQuantity(1);
$em->persist($ri);
$em->flush();

You should always get a correct record in your database table having both recipe_id and ingredient_id filled out correctly.

Checking out your code the following should also work, although I personally think this is more sensitive to mistakes:

$ri = new RecipeIngredient;
$ri->setIngredient($i);
$ri->setQuantity(1);
// here we assume that Recipe->addIngredient also does the setRecipe() for us and 
// that the cascade field is set correctly to cascade the persist on $ri
$r->addIngredient($ri);
$em->flush();

For further reading I would suggest the other topics on this subject, such as: Doctrine2: Best way to handle many-to-many with extra columns in reference table

score 0 · Answer 2 · answered Nov 22 '12 at 16:14

If I understand this model correctly the construction of a recipe and its associated recipeIngredients are concurrent. You might not have an id until you persist and without an id if receipeIngredient->setRecipe() is called the default null will be place in the recipeIngredient->recipe field. This is often handled with cascade: "persist" (not present for the recipe field in your example, but you can handle it explicitly in the controller:

/** 
  * Creates a new Recipe entity.
  *
  */
public function createAction(Request $request)
{
  $em = $this->getDoctrine()->getManager();
  $form = $this->createForm(new RecipeType());  
  $form->bind($request);

  if ($form->isValid()){
    $data = $form->getData();
    $recipeId = $data->getId();
    $recipeIngredients=$data->getIngredients();
    $recipe=$em->getRepository('reciperecipeBundle:Recipe')
               ->findOneById($RecipeId);
    if (null === $Recipe)
      {$Recipe=new Recipe();}  
    foreach ($recipeIngredients->toArray() as $k => $i){
      $recipeIngredient=$em->getRepository('reciperecipeBundle:recipeIngredient')
                           ->findOneById($i->getId());
      if (null === $recipeIngredient)
        {$recipeIngrediente=new RecipeIngredient();}  
      $recipe->addIngredient($i);

      // Next line *might* be handled by cascade: "persist"        
      $em->persist($recipeIngredient);
    }  
    $em->persist($Recipe);
    $em->flush();
    return $this->redirect($this->generateUrl('Recipe', array()));
  }   

  return $this->render('reciperecipeBundle:Recipe:new.html.twig'
                      ,array('form'   => $form->createView()));  
}

score 0 · Answer 3 · answered Oct 22 '13 at 11:13

Im not really sure if this would be a solution, but its easy yo try it, and probably it will help. When I create a relationshiop of this kind, I use to write another anotation, the @ORM\JoinColumn, like in this example:

We have an entity A, an entity B, and an class AB wich represents the relationships, and adds some other fields, like in you case.

My relationship would be as follows:

use Doctrine\ORM\Mapping as ORM;

/**
 *  
 *
 * @ORM\Table(name="a_rel_b")
 * @ORM\Entity
 */
class AB
{

    /**
     * @var integer
     *  @ORM\Id
     * @ORM\ManyToOne(targetEntity="A", inversedBy="b")
     * @ORM\JoinColumn(name="a_id", referencedColumnName="id")  
     **/

    private $a;


    /**
     * @var integer
     *  @ORM\Id
     * @ORM\ManyToOne(targetEntity="B", inversedBy="a")
     * @ORM\JoinColumn(name="b_id", referencedColumnName="id")
     **/
    private $b;
// ...

name means the name of the field in the relationship table, while referencedColumnName is the name of the id field in the referenced entity table (i.e b_id is a column in a_rel_b that references the column id in the table B )

score 0 · Answer 4 · edited May 23 '17 at 12:17

You can't, because it wouldn't be a relationship anymore [which is, by def, a subset of the cartesian product of the sets of the two original entities].

You need an intermediate entity, with references to both Recipe and Ingredient - call it RecipeElement, RecipeEntry or so, and add the fields you want.

Either, you can add a map to your Recipe, in which you save the attributes for each Ingredient you save, easy to maintain if there are no duplicates.

For further reading, have a look at this popular question.

Doctrine 2 Many to Many relation with additional columns in join table

4 Answers4