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

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.

/** @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?

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:

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.

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.

Solution 1:

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

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.

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

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

Solution 2:

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

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:

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.