Creating a one to many polymorphic relationship with doctrine

Question

Let me start by outlining the scenario. I have a Note object that can be assigned to many different objects

• A Book can have one or moreNotes.
• An Image can have one or more Notes.
• A Address can have one or more Notes.

What I envision the database to look like:

book

id | title           | pages
1  | harry potter    | 200
2  | game of thrones | 500

image

id | full      | thumb
2  | image.jpg | image-thumb.jpg

address

id | street      | city
1  | 123 street  | denver
2  | central ave | tampa

note

id | object_id | object_type | content      | date
1  | 1         | image       | "lovely pic" | 2015-02-10
2  | 1         | image       | "red tint"   | 2015-02-30
3  | 1         | address     | "invalid"    | 2015-01-05
4  | 2         | book        | "boobies"    | 2014-09-06
5  | 1         | book        | "prettygood" | 2016-05-05

The question is, how do I model this inside of Doctrine. Everything I have read about talks about single table inheritance without a one to many relationship.

To note (no pun intended ;), you may notice that note never needs unique attributes based on the object its related to.

Ideally I can do $address->getNotes() which would return the same type of Note object that $image->getNotes() would return.

At the very minimum the problem I am trying to solve is to avoid having three different tables: image_notes, book_notes and address_notes.

Answer 1

This question introduces unneccessary complexity into the application. Just because the notes have the same structure does not mean that they are the same entity. When modelling the database in 3NF, they are not the same entity because a note can not be moved from a Book to an Address. In your description there is a definitive parent-child relation between book and book_note, etc so model it as such.

More tables is not a problem for the database but unneccessary code complexity is, as this question demonstrates. It is just being clever for clevers sake. This is the trouble with ORMs, people stop doing full normalization and don't model the database correctly.

Answer 2

Personally I wouldn't use a superclass here. Think there's more of a case for an interface that would be implemented by Book, Image and Address:

interface iNotable
{
    public function getNotes();
}

Here's Book as an example:

class Book implements iNotable {
    /**
     * @OneToMany(targetEntity="Note", mappedBy="book")
     **/
    protected $notes;

    // ...        

    public function getNotes()
    {
        return $this->notes;
    }
}

Note then needs @ManyToOne relationships going the other way, only one of which will apply for each entity instance:

class Note {
    /**
     * @ManyToOne(targetEntity="Book", inversedBy="notes")
     * @JoinColumn
     **/
    protected $book;

    /**
     * @ManyToOne(targetEntity="Image", inversedBy="notes")
     * @JoinColumn
     **/
    protected $image;

    /**
     * @ManyToOne(targetEntity="Address", inversedBy="notes")
     * @JoinColumn
     **/
    protected $address;

    // ...
}

If you don't like the multiple references to each notable class here you could use inheritance and have something like an AbstractNotable superclass using single table inheritance. Although this might seem tidier now, the reason I wouldn't recommend it is as your data model grows you may need to introduce similar patterns that could eventually make the inheritance tree become unmanageable.

EDIT: It would also be useful to have Note validator to ensure data integrity by checking that exactly one of $book, $image or $address is set for each instance. Something like this:

/**
 * @PrePersist @PreUpdate
 */
public function validate()
{
    $refCount = is_null($book) ? 0 : 1;
    $refCount += is_null($image) ? 0 : 1;
    $refCount += is_null($address) ? 0 : 1;

    if ($refCount != 1) {
        throw new ValidateException("Note references are not set correctly");
    }
}

Answer 3

I add another answer after reading your question once more and realizing that you stated "I want to avoid adding another join table".

Make a Note base entity and extend this class in 3 different note entities for example: BookNote, AddressNote and ImageNote. In my solution the note table would look like this:

id | address_id | book_id | image_id | object_type | content      | date
1  | null       | null    | 1        | image       | "lovely pic" | 2015-02-10
2  | null       | null    | 1        | image       | "red tint"   | 2015-02-30
3  | 1          | null    | null     | address     | "invalid"    | 2015-01-05
4  | null       | 2       | null     | book        | "boobies"    | 2014-09-06
5  | null       | 1       | null     | book        | "prettygood" | 2016-05-05

You cannot use one common column object_id because of the foreign key constraints.

A NotesTrait with setters and getters for notes to prevent code duplication:

<?php

namespace Application\Entity;

use Doctrine\Common\Collections\Collection; 

/**
 * @property Collection $notes
 */
trait NotesTrait
{
    /**
     * Add note.
     *
     * @param Note $note
     * @return self
     */
    public function addNote(Note $note)
    {
        $this->notes[] = $note;

        return $this;
    }

    /**
     * Add notes.
     *
     * @param Collection $notes
     * @return self
     */
    public function addNotes(Collection $notes)
    {
        foreach ($notes as $note) {
            $this->addNote($note);
        }
        return $this;
    }

    /**
     * Remove note.
     *
     * @param Note $note
     */
    public function removeNote(Note $note)
    {
        $this->notes->removeElement($note);
    }

    /**
     * Remove notes.
     *
     * @param Collection $notes
     * @return self
     */
    public function removeNotes(Collection $notes)
    {
        foreach ($notes as $note) {
            $this->removeNote($note);
        }
        return $this;
    }

    /**
     * Get notes.
     *
     * @return Collection
     */
    public function getNotes()
    {
        return $this->notes;
    }
}

Your Note entity:

<?php

namespace Application\Entity;

/**
 * @Entity
 * @InheritanceType("SINGLE_TABLE")
 * @DiscriminatorColumn(name="object_type", type="string")
 * @DiscriminatorMap({"note"="Note", "book"="BookNote", "image"="ImageNote", "address"="AddressNote"})
 */
class Note
{
    /**
     * @var integer
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

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

    /**
     * @var string
     * @ORM\Column(type="date")
     */
    protected $date
}

Your BookNote entity:

<?php

namespace Application\Entity;

/**
 * @Entity
 */
class BookNote extends Note
{
    /** MANY-TO-ONE BIDIRECTIONAL, OWNING SIDE
     * @var Book
     * @ORM\ManyToOne(targetEntity="Application\Entity\Book", inversedBy="notes")
     * @ORM\JoinColumn(name="book_id", referencedColumnName="id", nullable=true)
     */    
    protected $book;
}

Your AddressNote entity:

<?php

namespace Application\Entity;

/**
 * @Entity
 */
class AddressNote extends Note
{
    /** MANY-TO-ONE BIDIRECTIONAL, OWNING SIDE
     * @var Address
     * @ORM\ManyToOne(targetEntity="Application\Entity\Address", inversedBy="notes")
     * @ORM\JoinColumn(name="address_id", referencedColumnName="id", nullable=true)
     */    
    protected $address;
}

Your ImageNote entity:

<?php

namespace Application\Entity;

/**
 * @Entity
 */
class ImageNote extends Note
{
    /** MANY-TO-ONE BIDIRECTIONAL, OWNING SIDE
     * @var Image
     * @ORM\ManyToOne(targetEntity="Application\Entity\Image", inversedBy="notes")
     * @ORM\JoinColumn(name="image_id", referencedColumnName="id", nullable=true)
     */    
    protected $image;
}

Your Book entity:

<?php

namespace Application\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\ArrayCollection;    

class Book
{
    use NotesTrait;

    /**
     * @var integer
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /** ONE-TO-MANY BIDIRECTIONAL, INVERSE SIDE
     * @var Collection
     * @ORM\OneToMany(targetEntity="Application\Entity\BookNote", mappedBy="book")
     */
    protected $notes;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->notes = new ArrayCollection();
    }
}

Your Address entity:

<?php

namespace Application\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\ArrayCollection;    

class Address
{
    use NotesTrait;

    /**
     * @var integer
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /** ONE-TO-MANY BIDIRECTIONAL, INVERSE SIDE
     * @var Collection
     * @ORM\OneToMany(targetEntity="Application\Entity\AddressNote", mappedBy="address")
     */
    protected $notes;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->notes = new ArrayCollection();
    }
}

Your Image entity:

<?php

namespace Application\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\ArrayCollection;    

class Image
{
    use NotesTrait;

    /**
     * @var integer
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /** ONE-TO-MANY BIDIRECTIONAL, INVERSE SIDE
     * @var Collection
     * @ORM\OneToMany(targetEntity="Application\Entity\ImageNote", mappedBy="image")
     */
    protected $notes;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->notes = new ArrayCollection();
    }
}

Answer 4

As you cant have several foreign keys on one column, you have 2 solutuons:

1. either make one sided one2many relations between Book/Image/Address and Note, so it would create a join table for each relation like book_note, image_note etc. holding the id pairs book.id-note.id etc. (I mean one2many instead of many2many because there usually is no point for a note to know which book or image or address it belongs to)

http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#one-to-many-unidirectional-with-join-table

2. or make multiple entities BookNote,ImageNote,AddressNote related to a book/image/address and a note and other data if for example one type of a note has some other data in it.

3. you can use single table inheritance as described in accepted answer to this question, but it is a solution similar to one proposed by Steven, which makes you hold additional data in the discriminator column.

Multiple JoinColumns in Symfony2 using Doctrine annotations?

Doctrine documentation also mentions a performance hit in certain situations.

http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/inheritance-mapping.html#performance-impact

Solution given by Steve Chambers solves a problem but you force the note table to hold unnecessary data(empty id columns for other objects)