理论2:在参考表中使用额外列处理多对多的最佳方法

I'm wondering what's the best, the cleanest and the most simply way to work with many-to-many relations in Doctrine2.

我想知道在教义2中，处理多对多关系的最好、最干净、最简单的方法是什么。

Let's assume that we've got an album like Master of Puppets by Metallica with several tracks. But please note the fact that one track might appears in more that one album, like Battery by Metallica does - three albums are featuring this track.

假设我们有一个像《木偶大师》这样的专辑，它有几个音轨。但是请注意一个事实，一个音轨可能出现在更多的一个专辑中，就像金属乐队的电池一样——有三张专辑是关于这首音轨的。

So what I need is many-to-many relationship between albums and tracks, using third table with some additional columns (like position of the track in specified album). Actually I have to use, as Doctrine's documentation suggests, a double one-to-many relation to achieve that functionality.

因此，我需要的是专辑和歌曲之间的多对多关系，使用带有一些附加列的第三个表(比如指定专辑中的歌曲位置)。实际上，正如Doctrine的文档所建议的，我必须使用一对多关系来实现这个功能。

/** @Entity() */
class Album {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="album") */
    protected $tracklist;

    public function __construct() {
        $this->tracklist = new \Doctrine\Common\Collections\ArrayCollection();
    }

    public function getTitle() {
        return $this->title;
    }

    public function getTracklist() {
        return $this->tracklist->toArray();
    }
}

/** @Entity() */
class Track {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @Column() */
    protected $title;

    /** @Column(type="time") */
    protected $duration;

    /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="track") */
    protected $albumsFeaturingThisTrack; // btw: any idea how to name this relation? :)

    public function getTitle() {
        return $this->title;
    }

    public function getDuration() {
        return $this->duration;
    }
}

/** @Entity() */
class AlbumTrackReference {
    /** @Id @Column(type="integer") */
    protected $id;

    /** @ManyToOne(targetEntity="Album", inversedBy="tracklist") */
    protected $album;

    /** @ManyToOne(targetEntity="Track", inversedBy="albumsFeaturingThisTrack") */
    protected $track;

    /** @Column(type="integer") */
    protected $position;

    /** @Column(type="boolean") */
    protected $isPromoted;

    public function getPosition() {
        return $this->position;
    }

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

    public function getAlbum() {
        return $this->album;
    }

    public function getTrack() {
        return $this->track;
    }
}

Sample data:

样本数据:

             Album
+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | Master of Puppets        |
|  2 | The Metallica Collection |
+----+--------------------------+

               Track
+----+----------------------+----------+
| id | title                | duration |
+----+----------------------+----------+
|  1 | Battery              | 00:05:13 |
|  2 | Nothing Else Matters | 00:06:29 |
|  3 | Damage Inc.          | 00:05:33 |
+----+----------------------+----------+

              AlbumTrackReference
+----+----------+----------+----------+------------+
| id | album_id | track_id | position | isPromoted |
+----+----------+----------+----------+------------+
|  1 |        1 |        2 |        2 |          1 |
|  2 |        1 |        3 |        1 |          0 |
|  3 |        1 |        1 |        3 |          0 |
|  4 |        2 |        2 |        1 |          0 |
+----+----------+----------+----------+------------+

Now I can display a list of albums and tracks associated to them:

现在我可以显示与之相关的专辑和歌曲列表:

$dql = '
    SELECT   a, tl, t
    FROM     Entity\Album a
    JOIN     a.tracklist tl
    JOIN     tl.track t
    ORDER BY tl.position ASC
';

$albums = $em->createQuery($dql)->getResult();

foreach ($albums as $album) {
    echo $album->getTitle() . PHP_EOL;

    foreach ($album->getTracklist() as $track) {
        echo sprintf("\t#%d - %-20s (%s) %s\n", 
            $track->getPosition(),
            $track->getTrack()->getTitle(),
            $track->getTrack()->getDuration()->format('H:i:s'),
            $track->isPromoted() ? ' - PROMOTED!' : ''
        );
    }   
}

The results are what I'm expecting, ie: a list of albums with their tracks in appropriate order and promoted ones being marked as promoted.

结果就是我所期望的，例如:一个专辑列表，它们的音轨按照适当的顺序排列，并且被标记为升级。

The Metallica Collection
    #1 - Nothing Else Matters (00:06:29) 
Master of Puppets
    #1 - Damage Inc.          (00:05:33) 
    #2 - Nothing Else Matters (00:06:29)  - PROMOTED!
    #3 - Battery              (00:05:13) 

So what's wrong?

This code demonstrates what's wrong:

这段代码演示了哪里出了问题:

foreach ($album->getTracklist() as $track) {
    echo $track->getTrack()->getTitle();
}

Album::getTracklist() returns an array of AlbumTrackReference objects instead of Track objects. I can't create proxy methods cause what if both, Album and Track would have getTitle() method? I could do some extra processing within Album::getTracklist() method but what's the most simply way to do that? Am I forced do write something like that?

getTracklist()返回一个AlbumTrackReference对象数组，而不是跟踪对象。我不能创建代理方法，如果相册和Track都有getTitle()方法呢?我可以在Album: getTracklist()方法中做一些额外的处理，但是最简单的方法是什么?我是*写这样的东西吗?

public function getTracklist() {
    $tracklist = array();

    foreach ($this->tracklist as $key => $trackReference) {
        $tracklist[$key] = $trackReference->getTrack();

        $tracklist[$key]->setPosition($trackReference->getPosition());
        $tracklist[$key]->setPromoted($trackReference->isPromoted());
    }

    return $tracklist;
}

// And some extra getters/setters in Track class

EDIT

@beberlei suggested to use proxy methods:

@beberlei建议使用代理方法:

class AlbumTrackReference {
    public function getTitle() {
        return $this->getTrack()->getTitle()
    }
}

That would be a good idea but I'm using that "reference object" from both sides: $album->getTracklist()[12]->getTitle() and $track->getAlbums()[1]->getTitle(), so getTitle() method should return different data based on the context of invocation.

这是一个好主意，但是我使用了来自两边的“引用对象”:$album->getTracklist()[12]->getTitle()和$track->getAlbums()[1]->getTitle()，因此getTitle()方法应该基于调用上下文返回不同的数据。

I would have to do something like:

我要做的是:

 getTracklist() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ....

 getAlbums() {
     foreach ($this->tracklist as $trackRef) { $trackRef->setContext($this); }
 }

 // ...

 AlbumTrackRef::getTitle() {
      return $this->{$this->context}->getTitle();
 }

And that's not a very clean way.

这不是一个很干净的方法。

14 个解决方案

#1

I've opened a similar question in the Doctrine user mailing list and got a really simple answer;

我在Doctrine用户邮件列表中打开了一个类似的问题，得到了一个非常简单的答案;

consider the many to many relation as an entity itself, and then you realize you have 3 objects, linked between them with a one-to-many and many-to-one relation.

把多对多关系看作一个实体本身，然后你意识到你有3个对象，它们之间有一对多和多对一关系。

http://groups.google.com/group/doctrine-user/browse_thread/thread/d1d87c96052e76f7/436b896e83c10868#436b896e83c10868

436年http://groups.google.com/group/doctrine-user/browse_thread/thread/d1d87c96052e76f7/436b896e83c10868 b896e83c10868

Once a relation has data, it's no more a relation !

关系一旦有了数据，就不再是关系了!

#2

From $album->getTrackList() you will alwas get "AlbumTrackReference" entities back, so what about adding methods from the Track and proxy?

从$album->getTrackList()你将会得到“AlbumTrackReference”实体，那么从跟踪和代理添加方法怎么样?

class AlbumTrackReference
{
    public function getTitle()
    {
        return $this->getTrack()->getTitle();
    }

    public function getDuration()
    {
        return $this->getTrack()->getDuration();
    }
}

This way your loop simplifies considerably, aswell as all other code related to looping the tracks of an album, since all methods are just proxied inside AlbumTrakcReference:

这样，您的循环就会大大简化，以及所有其他与录制专辑的曲目相关的代码，因为所有的方法都是在AlbumTrakcReference中进行的:

foreach ($album->getTracklist() as $track) {
    echo sprintf("\t#%d - %-20s (%s) %s\n", 
        $track->getPosition(),
        $track->getTitle(),
        $track->getDuration()->format('H:i:s'),
        $track->isPromoted() ? ' - PROMOTED!' : ''
    );
}

Btw You should rename the AlbumTrackReference (for example "AlbumTrack"). It is clearly not only a reference, but contains additional logic. Since there are probably also Tracks that are not connected to an album but just available through a promo-cd or something this allows for a cleaner separation also.

顺便说一句，您应该重命名AlbumTrackReference(例如“AlbumTrack”)。显然，它不仅是一个引用，而且还包含其他逻辑。因为可能也有一些歌曲不是与专辑相连的，而是通过促销cd或其他方式提供的，这也允许更清晰的分离。

#3

Nothing beats a nice example

没有什么比一个好例子更好的了

For people looking for a clean coding example of an one-to-many/many-to-one associations between the 3 participating classes to store extra attributes in the relation check this site out:

如果您正在寻找一个清晰的编码示例，说明3个参与类之间的一对多/多对一关联，以便在关系中存储额外属性，请查看此站点:

nice example of one-to-many/many-to-one associations between the 3 participating classes

3个参与类之间的一对多/多对一关联的好例子

Think about your primary keys

想想你的主键

Also think about your primary key. You can often use composite keys for relationships like this. Doctrine natively supports this. You can make your referenced entities into ids. Check the documentation on composite keys here

还要考虑你的主键。对于这样的关系，通常可以使用复合键。教义本身支持这一点。可以将引用的实体设置为ids。在这里检查复合键的文档

#4

I think I would go with @beberlei's suggestion of using proxy methods. What you can do to make this process simpler is to define two interfaces:

我想我同意@beberlei关于使用代理方法的建议。为了简化这个过程，您可以定义两个接口:

interface AlbumInterface {
    public function getAlbumTitle();
    public function getTracklist();
}

interface TrackInterface {
    public function getTrackTitle();
    public function getTrackDuration();
}

Then, both your Album and your Track can implement them, while the AlbumTrackReference can still implement both, as following:

然后，你的专辑和你的歌曲都可以实现它们，而AlbumTrackReference仍然可以实现这两种功能，如下所示:

class Album implements AlbumInterface {
    // implementation
}

class Track implements TrackInterface {
    // implementation
}

/** @Entity whatever */
class AlbumTrackReference implements AlbumInterface, TrackInterface
{
    public function getTrackTitle()
    {
        return $this->track->getTrackTitle();
    }

    public function getTrackDuration()
    {
        return $this->track->getTrackDuration();
    }

    public function getAlbumTitle()
    {
        return $this->album->getAlbumTitle();
    }

    public function getTrackList()
    {
        return $this->album->getTrackList();
    }
}

This way, by removing your logic that is directly referencing a Track or an Album, and just replacing it so that it uses a TrackInterface or AlbumInterface, you get to use your AlbumTrackReference in any possible case. What you will need is to differentiate the methods between the interfaces a bit.

这样，通过删除直接引用歌曲或专辑的逻辑，并替换它以便它使用TrackInterface或AlbumInterface，您可以在任何可能的情况下使用AlbumTrackReference。您需要的是在接口之间稍微区分一下方法。

This won't differentiate the DQL nor the Repository logic, but your services will just ignore the fact that you're passing an Album or an AlbumTrackReference, or a Track or an AlbumTrackReference because you've hidden everything behind an interface :)

这并不能区分DQL和存储库逻辑，但您的服务将忽略这样一个事实:您正在传递一个相册、一个AlbumTrackReference、一个跟踪或一个AlbumTrackReference，因为您已经将所有内容隐藏在一个接口后面:)

Hope this helps!

希望这可以帮助!

#5

First, I mostly agree with beberlei on his suggestions. However, you may be designing yourself into a trap. Your domain appears to be considering the title to be the natural key for a track, which is likely the case for 99% of the scenarios you come across. However, what if Battery on Master of the Puppets is a different version (different length, live, acoustic, remix, remastered, etc) than the version on The Metallica Collection.

首先，我基本上同意贝伯雷的建议。然而，你可能会把自己设计成一个陷阱。你的域名似乎认为标题是音轨的自然键，这可能是你遇到的99%的情况。然而，如果《木偶大师》的电池是一个不同的版本(不同长度、寿命、声音、混音、重新掌握等)，而不是Metallica系列的版本。

Depending on how you want to handle (or ignore) that case, you could either go beberlei's suggested route, or just go with your proposed extra logic in Album::getTracklist(). Personally, I think the extra logic is justified to keep your API clean, but both have their merit.

根据您希望处理(或忽略)这种情况的方式，您可以选择beberlei的建议路线，或者只使用您在Album::getTracklist()中建议的额外逻辑。就我个人而言，我认为保持API整洁是合理的，但两者都有各自的优点。

If you do wish to accommodate my use case, you could have Tracks contain a self referencing OneToMany to other Tracks, possibly $similarTracks. In this case, there would be two entities for the track Battery, one for The Metallica Collection and one for Master of the Puppets. Then each similar Track entity would contain a reference to each other. Also, that would get rid of the current AlbumTrackReference class and eliminate your current "issue". I do agree that it is just moving the complexity to a different point, but it is able to handle a usecase it wasn't previously able to.

如果您确实想要适应我的用例，您可以让轨迹包含一个自引用OneToMany到其他轨迹，可能是相似轨迹。在这种情况下，轨道电池将有两个实体，一个用于Metallica收藏，另一个用于木偶大师。然后，每个类似的跟踪实体将包含对彼此的引用。此外，这将消除当前的AlbumTrackReference类，并消除当前的“问题”。我同意它只是将复杂性转移到另一个点上，但是它能够处理以前不能处理的一个usecase。

#6

You ask for the "best way" but there is no best way. There are many ways and you already discovered some of them. How you want to manage and/or encapsulate association management when using association classes is entirely up to you and your concrete domain, noone can show you a "best way" I'm afraid.

你要求“最好的方法”，但没有最好的方法。有很多方法，你已经发现了其中的一些。在使用关联类时，您希望如何管理和/或封装关联管理完全取决于您和您的具体领域，恐怕没有人可以向您展示“最佳方式”。

Apart from that, the question could be simplified a lot by removing Doctrine and relational databases from the equation. The essence of your question boils down to a question about how to deal with association classes in plain OOP.

除此之外，这个问题还可以通过从等式中删除理论和关系数据库来简化。您的问题的本质可以归结为一个关于如何在普通OOP中处理关联类的问题。

#7

I was getting from a conflict with join table defined in an association class ( with additional custom fields ) annotation and a join table defined in a many-to-many annotation.

我遇到了关联类(带有附加自定义字段)注释中定义的连接表和多对多注释中定义的连接表之间的冲突。

The mapping definitions in two entities with a direct many-to-many relationship appeared to result in the automatic creation of the join table using the 'joinTable' annotation. However the join table was already defined by an annotation in its underlying entity class and I wanted it to use this association entity class's own field definitions so as to extend the join table with additional custom fields.

具有直接多对多关系的两个实体中的映射定义，似乎导致使用“可连接”注释自动创建连接表。但是，连接表已经由它的底层实体类中的注释定义，我希望它使用这个关联实体类自己的字段定义，以便用其他自定义字段扩展连接表。

The explanation and solution is that identified by FMaz008 above. In my situation, it was thanks to this post in the forum 'Doctrine Annotation Question'. This post draws attention to the Doctrine documentation regarding ManyToMany Uni-directional relationships. Look at the note regarding the approach of using an 'association entity class' thus replacing the many-to-many annotation mapping directly between two main entity classes with a one-to-many annotation in the main entity classes and two 'many-to-one' annotations in the associative entity class. There is an example provided in this forum post Association models with extra fields:

解释和解决方案是上面FMaz008标识的。在我的情况下，多亏了论坛“教义注释问题”的这篇文章。这篇文章提请注意关于许多单向关系的学说文献。请看关于使用“关联实体类”的方法的说明，这样就可以用主实体类中的一对多注释和关联实体类中的两个“多对一”注释直接替换两个主要实体类之间的多对多注释映射。有一个例子提供在这个论坛后的联系模型与额外的领域:

public class Person {

  /** @OneToMany(targetEntity="AssignedItems", mappedBy="person") */
  private $assignedItems;

}

public class Items {

    /** @OneToMany(targetEntity="AssignedItems", mappedBy="item") */
    private $assignedPeople;
}

public class AssignedItems {

    /** @ManyToOne(targetEntity="Person")
    * @JoinColumn(name="person_id", referencedColumnName="id")
    */
private $person;

    /** @ManyToOne(targetEntity="Item")
    * @JoinColumn(name="item_id", referencedColumnName="id")
    */
private $item;

}

#8

This really useful example. It lacks in the documentation doctrine 2.

这真的有用的例子。它缺乏文献主义2。

Very thank you.

非常谢谢你。

For the proxies functions can be done :

对于代理函数可以这样做:

class AlbumTrack extends AlbumTrackAbstract {
   ... proxy method.
   function getTitle() {} 
}

class TrackAlbum extends AlbumTrackAbstract {
   ... proxy method.
   function getTitle() {}
}

class AlbumTrackAbstract {
   private $id;
   ....
}

and

和

/** @OneToMany(targetEntity="TrackAlbum", mappedBy="album") */
protected $tracklist;

/** @OneToMany(targetEntity="AlbumTrack", mappedBy="track") */
protected $albumsFeaturingThisTrack;

#9

What you are referring to is metadata, data about data. I had this same issue for the project I am currently working on and had to spend some time trying to figure it out. It's too much information to post here, but below are two links you may find useful. They do reference the Symfony framework, but are based on the Doctrine ORM.

你指的是元数据，关于数据的数据。对于我目前正在进行的项目，我也有同样的问题，不得不花些时间去解决它。在这里发布的信息太多了，下面是两个你可能会发现有用的链接。他们确实参考了共生框架，但基于教义ORM。

http://melikedev.com/2010/04/06/symfony-saving-metadata-during-form-save-sort-ids/

http://melikedev.com/2010/04/06/symfony-saving-metadata-during-form-save-sort-ids/

http://melikedev.com/2009/12/09/symfony-w-doctrine-saving-many-to-many-mm-relationships/

http://melikedev.com/2009/12/09/symfony-w-doctrine-saving-many-to-many-mm-relationships/

Good luck, and nice Metallica references!

祝你好运，还有漂亮的金属乐队!

#10

The solution is in the documentation of Doctrine. In the FAQ you can see this :

解决方案在教条的文档中。在FAQ中，你可以看到以下内容:

http://docs.doctrine-project.org/en/2.1/reference/faq.html#how-can-i-add-columns-to-a-many-to-many-table

http://docs.doctrine-project.org/en/2.1/reference/faq.html how-can-i-add-columns-to-a-many-to-many-table

And the tutorial is here :

教程在这里:

http://docs.doctrine-project.org/en/2.1/tutorials/composite-primary-keys.html

http://docs.doctrine-project.org/en/2.1/tutorials/composite-primary-keys.html

So you do not anymore do a manyToMany but you have to create an extra Entity and put manyToOne to your two entities.

所以你不再做很多很多，但是你必须创建一个额外的实体，把许多放到你的两个实体中。

ADD for @f00bar comment :

添加@f00bar评论:

it's simple, you have just to to do something like this :

很简单，你只需要做这样的事情:

Article  1--N  ArticleTag  N--1  Tag

So you create an entity ArticleTag

所以你创建了一个实体article标签。

ArticleTag:
  type: entity
  id:
    id:
      type: integer
      generator:
        strategy: AUTO
  manyToOne:
    article:
      targetEntity: Article
      inversedBy: articleTags
  fields: 
    # your extra fields here
  manyToOne:
    tag:
      targetEntity: Tag
      inversedBy: articleTags

I hope it helps

我希望这有助于

#11

Unidirectional. Just add the inversedBy:(Foreign Column Name) to make it Bidirectional.

单向。只需添加反向by:(外列名)使其具有双向性。

# config/yaml/ProductStore.dcm.yml
ProductStore:
  type: entity
  id:
    product:
      associationKey: true
    store:
      associationKey: true
  fields:
    status:
      type: integer(1)
    createdAt:
      type: datetime
    updatedAt:
      type: datetime
  manyToOne:
    product:
      targetEntity: Product
      joinColumn:
        name: product_id
        referencedColumnName: id
    store:
      targetEntity: Store
      joinColumn:
        name: store_id
        referencedColumnName: id

I hope it helps. See you.

我希望它有帮助。见到你。

#12

You may be able to achieve what you want with Class Table Inheritance where you change AlbumTrackReference to AlbumTrack:

您可以通过类表继承实现您想要的东西，您可以将AlbumTrackReference更改为AlbumTrack:

class AlbumTrack extends Track { /* ... */ }

And getTrackList() would contain AlbumTrack objects which you could then use like you want:

getTrackList()将包含AlbumTrack对象，然后可以像您希望的那样使用这些对象:

foreach($album->getTrackList() as $albumTrack)
{
    echo sprintf("\t#%d - %-20s (%s) %s\n", 
        $albumTrack->getPosition(),
        $albumTrack->getTitle(),
        $albumTrack->getDuration()->format('H:i:s'),
        $albumTrack->isPromoted() ? ' - PROMOTED!' : ''
    );
}

You will need to examine this throughly to ensure you don't suffer performance-wise.

您将需要仔细检查这一点，以确保您不会在性能方面受到影响。

Your current set-up is simple, efficient, and easy to understand even if some of the semantics don't quite sit right with you.

您当前的设置是简单的、高效的、易于理解的，即使有些语义不太适合您。

#13

While getting all album tracks form inside album class, you'll generate one more query for one more record. That's because of proxy method. There's another example of my code (see last post in topic): http://groups.google.com/group/doctrine-user/browse_thread/thread/d1d87c96052e76f7/436b896e83c10868#436b896e83c10868

当在album类中获取所有的album track表单时，您将为更多的记录生成一个查询。这是因为代理方法。我的代码还有另一个例子(见最后一篇文章):http://groups.google.com/group/doctrine- user/browse_thread/d1d87c96052e76f7 /436b896e83c10868# 436b896e83e83e83c10868

Is there any other method to resolve that? Isn't a single join a better solution?

还有其他方法来解决这个问题吗?一个加入不是更好的解决方案吗?

#14

Here is the solution as described in the Doctrine2 Documentation

这里是如理论2文档所描述的解决方案

<?php
use Doctrine\Common\Collections\ArrayCollection;

/** @Entity */
class Order
{
    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /** @ManyToOne(targetEntity="Customer") */
    private $customer;
    /** @OneToMany(targetEntity="OrderItem", mappedBy="order") */
    private $items;

    /** @Column(type="boolean") */
    private $payed = false;
    /** @Column(type="boolean") */
    private $shipped = false;
    /** @Column(type="datetime") */
    private $created;

    public function __construct(Customer $customer)
    {
        $this->customer = $customer;
        $this->items = new ArrayCollection();
        $this->created = new \DateTime("now");
    }
}

/** @Entity */
class Product
{
    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /** @Column(type="string") */
    private $name;

    /** @Column(type="decimal") */
    private $currentPrice;

    public function getCurrentPrice()
    {
        return $this->currentPrice;
    }
}

/** @Entity */
class OrderItem
{
    /** @Id @ManyToOne(targetEntity="Order") */
    private $order;

    /** @Id @ManyToOne(targetEntity="Product") */
    private $product;

    /** @Column(type="integer") */
    private $amount = 1;

    /** @Column(type="decimal") */
    private $offeredPrice;

    public function __construct(Order $order, Product $product, $amount = 1)
    {
        $this->order = $order;
        $this->product = $product;
        $this->offeredPrice = $product->getCurrentPrice();
    }
}