Posts tagged Doctrine

Pretty much one of the hardest things I had to get my head around, saving many-to-many (M:M) relationships in Symfony (using Doctrine), but after some mindset adjustment and a lot of googling, I found it to be very easy to implement. It took several weeks and perusing several different google searches to piece together enough information to finally get my head around this process, hopefully this post will save some babies from being punched due to frustration.

Schema first. Say you have a M:M relationship between books and authors, meaning that an author can write more than one book (many) and a book can have more than one author (many). The schema to support this requires that we have a 3rd table, to which I refer as a linking table. The linking table’s main purpose is to link data from the author’s table to the data from the book’s table, which forces us into 2NF (second normal form), by ensuring we are not saving duplicate data in either the author table or the book table.

Your schema should look something like the following (remember that your table engines must be innodb to establish a FK relationship):

Author_Book Many-To-Many

Now we need to rebuild the schema so we can rebuild the model to take advantage of these relationships established by the schema.

php symfony doctrine:build-schema

Now that the schema has been rebuilt, we need to do some manual edits to ensure the model files are created correctly. So open /config/doctrine/schema.yml and you should see something like:

Author:
  tableName: author
  columns:
    ...
  relations:
    LinkingAuthorBook:
      local: id
      foreign: book_id
      type: many
Book:
  tableName: book
  columns:
    ...
  relations:
    LinkingAuthorBook:
      local: id
      foreign: author_id
      type: many

After

Author:
  tableName: author
  columns:
    ...
  relations:
    Book:
      class: Book
      refClass: LinkingAuthorBook #This will allow you to reference Book rows from an Author object
      local: author_id #Local value refers to the current object, in this case Author
      foreign: book_id #Foreign value refers to the object you wish to link to from Author, in this case Book
Book:
  tableName: book
  columns:
    ...
  relations:
    Author:
      class: Author
      refClass: LinkingAuthorBook #This will allow you to reference Author rows from a Book object
      local: book_id #Local value refers to the current object, in this case Author
      foreign: author_id #Foreign value refers to the object you wish to link to from Book, in this case Author

Note the changes made to the after schema.yml file. We added ‘class’ and most importantly, ‘refClass’. You can read more information about refClass @ doctrine’s website.

Now rebuild the model:

php symfony doctrine:build-model

The auto-generated classes created after rebuilding the model will be aware of the M:M relationship you have established in your schema which results in saving these with minimal effort.

As part of the rebuild “stack”, you must also rebuild your filters and forms, and clear your cache. So be sure to do the following as well:

php symfony doctrine:build-filters
php symfony doctrine:build-forms
php symfony cc

Now lets see saving a M:M relationship in action, lets add a select double list to your Author form (assuming you have sfFormExtraPlugin installed), just add the following to /lib/form/doctrine/AuthorForm.class.php:

$this->widgetSchema['book_list']->setOption('renderer_class', 'sfWidgetFormSelectDoubleList');

When you render your Author form, you should now have a double list with all the books you have in your book table. Select a few, then click save, you should see that they were saved against the Author you are currently editing. You can remove, add, etc as you see fit.

That’s it! You set up your app to handle the saving of M:M relationships with (hopefully) minimal effort.

—Edit 5.25.2010

Some people are having a problem with this example with respect to the linking table in the given example. As far as I know, the linking table MUST be present in your schema and available via the resulting model class, otherwise doctrine will not know how to store the link relationship between book and author.