This topic is a cross-post of this question at StackOverflow - stackoverflow.com/questions/19104742/yii-relational-active-record-not-loading-duplicate-related-entities (can’t add this as a link as I’m new to this forum and I’m not allowed to).
I’m using the version 1.1.13 of Yii. Got the following problem with relational active records.
Have three tables created in MySQL database: tbl_playlist, tbl_track and tbl_playlist_track. Tracks belong to playlists, MANY_MANY relation is defined in the Playlist model, tbl_playlist_track is the relational table implementing the relation.
Tables:
CREATE TABLE IF NOT EXISTS `tbl_playlist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `tbl_track` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(256) NOT NULL,
`added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `tbl_playlist_track` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`playlist_id` int(11) NOT NULL,
`track_id` int(11) NOT NULL,
`position` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `playlist_id` (`playlist_id`),
KEY `track_id` (`track_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
ALTER TABLE `tbl_playlist_track`
ADD CONSTRAINT `tbl_playlist_track_ibfk_1` FOREIGN KEY (`playlist_id`) REFERENCES `tbl_playlist` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `tbl_playlist_track_ibfk_2` FOREIGN KEY (`track_id`) REFERENCES `tbl_track` (`id`) ON DELETE CASCADE;
Relation declared in the Playlist model:
public function relations()
{
return array(
'tracks' => array(self::MANY_MANY,
'Track',
'tbl_playlist_track(playlist_id, track_id)',
'order' => 'position ASC')
);
}
The only additional field in the tbl_playlist_track is ‘position’, which is used for sorting tracks in a playlist.
The problem I face with this setup is that if I have multiple entries in tbl_playlist_track with the same values of FK’s playlist_id and track_id (i.e. if the same track is added to a playlist several times) then when I load a Playlist model using the ‘with’ operator for eager loading - it instantiates only Track models with unique track_id values.
For example, if I have for a playlist with id 1 tracks added to tbl_playlist_track with ids 3 3 5 1 6 1 and I run this code:
$playlist = Playlist::model()->with('tracks')->findByPk(1);
foreach($playlist->tracks as $track)
{
print $track->id." ";
}
then the output of the script will look like this:
3 5 1 6
I’ve checked the SQL query generated by Yii and it’s correct - it joins tables and returns all rows. So it must be related to the way Yii instantiates related model entities.
I found a blog post (hollyii.blogspot.be/2011/08/avoiding-duplicate-records-when-using.html) which made me believe that, actually, the default behavior should be the opposite - duplicate Track instances should have been created.
