Doctrine2: Best way to handle many-to-many with extra columns in reference table
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.
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.
Solution 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:
nice example of one-to-many/many-to-one associations between the 3 participating classes
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
Solution 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:
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:
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.
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 :)
Hope this helps!
Solution 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.
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.
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.
Related videos on Youtube
Crozin
Updated on November 22, 2020Comments
-
Crozin over 3 years
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 ofAlbumTrackReference
objects instead ofTrack
objects. I can't create proxy methods cause what if both,Album
andTrack
would havegetTitle()
method? I could do some extra processing withinAlbum::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()
, sogetTitle()
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.
-
Daniel about 11 yearsHow do you handle the AlbumTrackReference? For Example $album->addTrack() or $album->removeTrack()?
-
Vinícius Fagundes over 8 yearsI didn't understand you comment about context. In my opnion the data doesn't depend on context. About
$album->getTracklist()[12]
isAlbumTrackRef
object, so$album->getTracklist()[12]->getTitle()
will return always the title of the track (if you are using the proxy method). While$track->getAlbums()[1]
isAlbum
object, so$track->getAlbums()[1]->getTitle()
will return always the title of the album. -
Vinícius Fagundes over 8 yearsAnother idea is using on
AlbumTrackReference
two proxy methods,getTrackTitle()
andgetAlbumTitle
.
-
-
Crozin over 13 yearsProxy methods doesn't solve the problem in 100% (check my edit).
Btw You should rename the AlbumT(...)
- good point -
beberlei over 13 yearsWhy dont you have two methods? getAlbumTitle() and getTrackTitle() on the AlbumTrackReference object? Both proxy to their respective subobjects.
-
Crozin over 13 yearsThe goal is the most natural object API.
$album->getTracklist()[1]->getTrackTitle()
is as good/bad as$album->getTracklist()[1]->getTrack()->getTitle()
. However it seems that I'd have to have two different classes: one for album->track references and another for track->albums references - and that's too hard to implement. So probably that's the best solution so far... -
Spontifixus over 11 yearsWhilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
-
m14t about 9 yearsUpdated links: 1 - FAQ, 2 - Tutorials: Composite Primary Keys
-
Stphane about 9 yearsDoes anybody know how can I get the doctrine command line tool to generate this new entity as a yml schema file ? This command:
app/console doctrine:mapping:import AppBundle yml
still generate manyToMany relation for the original two tables and simply ignore the third table instead of concidering it as an entity:/
-
Stphane about 9 yearsThat's exacly what I was looking for, thank you ! Unfortunatly, there is no yml example for the third use case !
:(
Could anyone share an exabple of the third use case using yml format ? I would really appriace:#
-
Mirza Selimovic about 9 yearsi've added to the answer your case ;)
-
Gatunox almost 9 yearsIt's incorrect. The entity does not have to be with id (id) AUTO. That's wrong, I'm trying to create the correct example
-
Gatunox almost 9 yearsi will post a new answer to get if formatted correctly
-
panda about 8 yearswhat is the different between
foreach ($album->getTracklist() as $track) { echo $track->getTrack()->getTitle(); }
provided by @Crozin andconsider the relationship as an entity
? I think what he want to ask is how to skip the relational entity and retrieving the title of a track by usingforeach ($album->getTracklist() as $track) { echo $track->getTitle(); }
-
Onion about 8 years"Once a relation has data, it's no more a relation" This was really enlightening. I just couldn't think about a relation from an entity perspective!
-
tylerism over 6 yearsWhat about if the relationship was already created and used as a many to many. We realized that we needed extra fields in our many to many so we created a different entity. The trouble is, with existing data, and an existing table with the same name, it doesn't seem to want to be friends. Has anyone tried this before?
-
Jakumi over 4 yearsFor those wondering: creating an Entity with the (already existing) many-to-many jointable as its table works, however, the entities holding the many-to-many must be adapted to instead one-to-many to the new entity. also interfaces to the outside (getters/setters for the former many-to-many) most likely have to be adapted.