可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
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.
回答1:
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");
}
}
回答2:
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.
回答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();
}
}
回答4:
The closest you can get is by using a unidirectional One-To-Many with a join table. In doctrine this is done with a unidirectional Many-To-Many with a unique constraint on the join column.
The disadvantage of this solution is that it is unidirectional meaning that your note is not aware of the owning side of the relationship. But at first glance it looks like this should not be a problem in your case.
You will lose the object_id and object_type column in the note table.
In full code this would look like this:
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 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;
/**
* @ORM\ManyToMany(targetEntity="Note")
* @ORM\JoinTable(name="book_notes",
* inverseJoinColumns={@ORM\JoinColumn(name="note_id", referencedColumnName="id")},
* joinColumns={@ORM\JoinColumn(name="book_id", referencedColumnName="id", unique=true)}
* )
*/
$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;
/**
* @ORM\ManyToMany(targetEntity="Note")
* @ORM\JoinTable(name="address_notes",
* inverseJoinColumns={@ORM\JoinColumn(name="note_id", referencedColumnName="id")},
* joinColumns={@ORM\JoinColumn(name="address_id", referencedColumnName="id", unique=true)}
* )
*/
$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;
/**
* @ORM\ManyToMany(targetEntity="Note")
* @ORM\JoinTable(name="image_notes",
* inverseJoinColumns={@ORM\JoinColumn(name="note_id", referencedColumnName="id")},
* joinColumns={@ORM\JoinColumn(name="image_id", referencedColumnName="id", unique=true)}
* )
*/
$notes;
/**
* Constructor
*/
public function __construct()
{
$this->notes = new ArrayCollection();
}
}
回答5:
As you cant have several foreign keys on one column, you have 2 solutuons:
- 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
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.
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)
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 男人必须洒脱 的文章《Creating a one to many polymorphic relationship wi》','https://www.manongdao.com/article-735825.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}