Databases
StackOverflow2013
Postsdbo
PODoctrine2: Best way to handle many-to-many with extra columns in reference table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODoctrine2: Best way to handle many-to-many with extra columns in reference table
    primarykey
    data
    text
    <p>I'm wondering what's the best, the cleanest and the most simply way to work with many-to-many relations in Doctrine2. </p> <p>Let's assume that we've got an album like <a href="http://www.last.fm/music/Metallica/Master+of+Puppets" rel="noreferrer"><em>Master of Puppets</em> by Metallica</a> with several tracks. But please note the fact that one track might appears in more that one album, like <a href="http://www.last.fm/music/Metallica/Master+of+Puppets/Battery" rel="noreferrer"><em>Battery</em> by Metallica</a> does - three albums are featuring this track.</p> <p>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.</p> <pre><code>/** @Entity() */ class Album { /** @Id @Column(type="integer") */ protected $id; /** @Column() */ protected $title; /** @OneToMany(targetEntity="AlbumTrackReference", mappedBy="album") */ protected $tracklist; public function __construct() { $this-&gt;tracklist = new \Doctrine\Common\Collections\ArrayCollection(); } public function getTitle() { return $this-&gt;title; } public function getTracklist() { return $this-&gt;tracklist-&gt;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-&gt;title; } public function getDuration() { return $this-&gt;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-&gt;position; } public function isPromoted() { return $this-&gt;isPromoted; } public function getAlbum() { return $this-&gt;album; } public function getTrack() { return $this-&gt;track; } } </code></pre> <p>Sample data:</p> <pre><code> 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 | +----+----------+----------+----------+------------+ </code></pre> <p>Now I can display a list of albums and tracks associated to them:</p> <pre><code>$dql = ' SELECT a, tl, t FROM Entity\Album a JOIN a.tracklist tl JOIN tl.track t ORDER BY tl.position ASC '; $albums = $em-&gt;createQuery($dql)-&gt;getResult(); foreach ($albums as $album) { echo $album-&gt;getTitle() . PHP_EOL; foreach ($album-&gt;getTracklist() as $track) { echo sprintf("\t#%d - %-20s (%s) %s\n", $track-&gt;getPosition(), $track-&gt;getTrack()-&gt;getTitle(), $track-&gt;getTrack()-&gt;getDuration()-&gt;format('H:i:s'), $track-&gt;isPromoted() ? ' - PROMOTED!' : '' ); } } </code></pre> <p>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.</p> <pre><code>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) </code></pre> <h2>So what's wrong?</h2> <p>This code demonstrates what's wrong:</p> <pre><code>foreach ($album-&gt;getTracklist() as $track) { echo $track-&gt;getTrack()-&gt;getTitle(); } </code></pre> <p><code>Album::getTracklist()</code> returns an array of <code>AlbumTrackReference</code> objects instead of <code>Track</code> objects. I can't create proxy methods cause what if both, <code>Album</code> and <code>Track</code> would have <code>getTitle()</code> method? I could do some extra processing within <code>Album::getTracklist()</code> method but what's the most simply way to do that? Am I forced do write something like that?</p> <pre><code>public function getTracklist() { $tracklist = array(); foreach ($this-&gt;tracklist as $key =&gt; $trackReference) { $tracklist[$key] = $trackReference-&gt;getTrack(); $tracklist[$key]-&gt;setPosition($trackReference-&gt;getPosition()); $tracklist[$key]-&gt;setPromoted($trackReference-&gt;isPromoted()); } return $tracklist; } // And some extra getters/setters in Track class </code></pre> <h1>EDIT</h1> <p>@beberlei suggested to use proxy methods:</p> <pre><code>class AlbumTrackReference { public function getTitle() { return $this-&gt;getTrack()-&gt;getTitle() } } </code></pre> <p>That would be a good idea but I'm using that "reference object" from both sides: <code>$album-&gt;getTracklist()[12]-&gt;getTitle()</code> and <code>$track-&gt;getAlbums()[1]-&gt;getTitle()</code>, so <code>getTitle()</code> method should return different data based on the context of invocation.</p> <p>I would have to do something like:</p> <pre><code> getTracklist() { foreach ($this-&gt;tracklist as $trackRef) { $trackRef-&gt;setContext($this); } } // .... getAlbums() { foreach ($this-&gt;tracklist as $trackRef) { $trackRef-&gt;setContext($this); } } // ... AlbumTrackRef::getTitle() { return $this-&gt;{$this-&gt;context}-&gt;getTitle(); } </code></pre> <p>And that's not a very clean way.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USS.L. Barth
    1. USCrozin
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODoctrine2: Best way to handle many-to-many with extra columns in reference table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODoctrine2: Best way to handle many-to-many with extra columns in reference table
      1. USclarkstachio
      1. VTFavorite
    3. VO
      singulars
      1. PODoctrine2: Best way to handle many-to-many with extra columns in reference table
      1. USCrozin
      1. VTBountyStart
    1. COHow do you handle the AlbumTrackReference? For Example $album->addTrack() or $album->removeTrack()?
      singulars
      1. PODoctrine2: Best way to handle many-to-many with extra columns in reference table
      1. USDaniel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload