Yii 1.1: Accessing data in a join table with the related models

29 followers

Sometimes the right place to store application data is in a join table. For example, movie viewers either like or don’t like the movies they watched.

Movie viewers

EER diagram

CREATE  TABLE viewer (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(45))
 
CREATE  TABLE movie (
  id INT NOT NULL PRIMARY KEY,
  title VARCHAR(45))
 
CREATE  TABLE viewer_watched_movie (
  viewer_id INT NOT NULL,
  movie_id INT NOT NULL,
  liked TINYINT(1),
  PRIMARY KEY (viewer_id, movie_id),
  CONSTRAINT fk_viewer_watched
    FOREIGN KEY (movie_id) REFERENCES movie (id)
  CONSTRAINT fk_movie_watched_by
    FOREIGN KEY (viewer_id) REFERENCES viewer (id))

So I need a Viewer model class and a Movie model class. Movie and Viewer are related n:m, i.e. many-to-many.

Property liked goes in the join table. This is correct relational DB design for data that belong to the relationship between the related entities—liked belongs to neither Movie nor Viewer. But how to access the liked attribute in a sensible way using relational Active Record?

Accessing join table properties

From the context of a Viewer I can get the Movies he or she watched with this relation:

class Viewer extends CActiveRecord {
    ...
    public function relations()     {
        return array(
            'movies' => array(self::MANY_MANY, 'Movie', 
                'viewer_watched_movie(viewer_id, movie_id)'),
    ...

Using it like this:

$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->movies as $movie)
        echo $viewer . ' watched ' . $movie->title;

[This will perform poorly because it lazily loads Movies one at a time in the loop. I can pep it up thus: Viewer::model()->with('movies')->findByPk($id)]

The problem

How can I add the liked property to that echo in the loop? For example, is there a way to write relations and/or getters so I can write?:

$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->movies as $movie)
        echo $viewer . ($movie->liked ? ' liked ' : ' didn’t like ') 
            . $movie->title;

If there is a way (in Yii 1.1.8, without extending or adding behaviors to CActiveRecord) then I haven't found it.

The nearest solution I've found

This trick is hard to figure out from the documentation, hence this Wiki article. It uses the through relation option introduced in Yii 1.1.7. With through I can relate one model to another via an intermediary, e.g. a join table.

class Viewer extends CActiveRecord {
    ...
    public function relations()     {
        return array(
            'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),
            'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 
                'through' => 'watched'),
    ...

So now I need a model for the join table called ViewerWatchedMovie. There is no longer an explicit MANY_MANY relation—chaining two HAS_MANY achieves n:m-ness.

Now I can write:

$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->watched as $i => $watched)
        echo $viewer . ($watched->liked ? ' liked ' : ' didn’t like ') 
            . $viewer->movies[$i]->title;

This is an improvement. It's not pretty (typical relational AR, imnsho) but it works.

To make it eager: Viewer::model()->with('watched', 'movies')->findByPk($id).

I have it on good authority that the coherence of indexes of the two arrays $viewer->watched and $viewer->movies is assured. But this fact (feature?) is not documented so it makes me nervous. That's why I prefer a variation.

Add a relation to the ViewerWatchedMovie model:

class ViewerWatchedMovie extends CActiveRecord {
    ...
    public function relations() {
        return array(
            'movie' => array(self::HAS_ONE, 'Movie', 'movie_id'),
    ...

And that allows:

$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->watched as $watched)
        echo $viewer . ($watched->liked ? ' liked ' : ' didn’t like ') 
            . $watched->movie->title;

which gets rid of that scary indexing trick.

Eager version: Viewer::model()->with('watched.movie')->findByPk($id). You can add 'movies' to the with() if needed.

That's the best I can manage. I didn't get to $movie[$i]->liked. Clever magic getters allow this but I don't think it's worth it.

Total 7 comments

#9158 report it
ohrsman at 2012/07/24 03:33pm
i do it this way (works jsut with composite primary)

to get a extra column to mm table like sorting. Car has many files. And Files of Car has sorting.

Model Cars

class Cars extends CActiveRecord {
...
  public function relations() {
 
    return array(
      'files' => array(self::MANY_MANY, 'Files', 'files_has_reference(id_foreign, id_locale)',
        //'condition' => 'files_files.model = "cars"',
        //'order' => 'files_files.sorting_foreign',
        'with' => 'files_has_reference',
      ),
    );
  }
...
}//class end

Model Files

class Files extends CActiveRecord {
...
  public function relations() {
    return array(
      'files_has_reference' => array(self::HAS_ONE, 'FilesHasReference', 'id_locale'),
    );
  }
...
}//class end

Model FilesHasReference

class FilesHasReferenceextends CActiveRecord {
...
  public function relations() {
    return array(
 
    );
  }
...
}//class end

Table Structure

CREATE TABLE IF NOT EXISTS `cars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `file_name` (`file_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
 
CREATE TABLE IF NOT EXISTS `files_has_reference` (
  `id_locale` int(11) NOT NULL DEFAULT '0',
  `id_foreign` int(11) NOT NULL DEFAULT '0',
  `sorting_foreign` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_locale`,`id_foreign`),
  KEY `id_locale` (`id_locale`),
  KEY `id_foreign` (`id_foreign`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i can do then in one some bigger query

like SELECT * FROM files files INNER JOIN files_has_reference files_files ON (files_files.id_foreign= MY_CARS_ID) AND (files.id=files_files.id_locale) LEFT OUTER JOIN files_has_reference files_has_reference ON (files_has_reference.id_locale=files.id)

foreach car->files-> as $file
//data from mm table
$file->files_has_reference->sorting_foreign

hope it helps someone

#8671 report it
Coksnuss at 2012/06/18 01:50pm
HAS_ONE Relation in join table does not work

I've followed your guide and can now successfully access the 'liked' property (sticking to the example). But the "movie" relation from "viewer_watched_movie" does not work in my case. I think that is because the primary key is in fact a composite key which is not beeing used when finding the correct related entry in the "movie" table. In my case EVERY instance of "viewer_watched_movie" has primaryKey "1" which is used to find related records. Therefore the movie that i get trough this relation is always the same movie (the one with id = 1). Is there a way to tell the relation to use an other column to compare with?

// EDIT: It now works. The foreign key in the relation was wrong. It should be "array('id' => 'movie_id')" instead of just "movie_id" and the "watched" relation in the Viewer model should have the additional option 'with' => 'movie' in order to avoid additional SQL Querys.

#7483 report it
tybetx at 2012/03/25 05:32pm
My solution

In http://www.yiiframework.com/doc/guide/1.1/en/database.arr we can read "In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY". So we can write:

class Viewer extends CActiveRecord {
    ...
    public function relations()     {
        return array(
            'watched_movie' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id',
                'with'=>'movie',
                'together'=>false,
           ),
    ...
class ViewerWatchedMovie extends CActiveRecord {
    ...
    public function relations()     {
        return array(
            'movie' => array(self::BELONGS_TO, 'Movie', 'movie_id'),
    ...

an then we can write:

$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->watched_movie as $watched)
        echo $viewer . ($watched->liked ? ' liked ' : ' didn’t like ') 
            . $watched->movie->title;
#7263 report it
fleuryc at 2012/03/08 04:02am
Totally agree with thefsb

Hi,

I also really think there should be a native way in Yii to access the data in the association class in MANY_MANY relations...

Like adding the property from the association model to the related model. In this examle, I think we should be able to call :

$viewer = Viewer::model->findByPk($id);
foreach ($viewer->movies as $movie)
    echo $viewer . ' watched ' . $movie->title . ' and found it ' .
        ($movie->liked ? 'awsome' : 'lame') . '!';
#6008 report it
fsb at 2011/12/05 10:42am
These relations are properly identifying relations

@Asgotath: what you have done is change the two relationships into non-identifying relations. This is not correct for this example.

The entity represented by a row in viewer_watched_movie is a relationship, and it cannot exist except by being owned by the entities it relates. Hence its relations should be identifying relations.

Put it another way: unless there is a reason why a viewer_watched_movie row should exist with its viewer and/or movie ids going nowhere, it should not have its own independent PK.

Or, yet another way: if ON DELETE SET NULL is appropriate then you have a non-identifying relation and the join table needs its own PK. But clearly this application needs ON DELETE CASCADE for both relations. That makes all the difference.

If the entity in the join table is a relation and its every column does no more than elaborate on the nature of the relation then it is still a join table.

#6007 report it
Asgaroth at 2011/12/05 09:55am
mmm

mmm I beleave the problem is not Yii, but the way you are designing you database schema, I was taught, that when you have a many many relationship then you have an intermediate table, just like you have. however, if that table needs to hold other data (in your case whether the viewer liked the movie or not), them that table should no longer have a composite key.

Instead the table becomes an entity itself, and is no longer just a relational table. and you know, an entity should have its own primary key (preferably numerical, unsigned, and auto-incremental), and those keys from viewer and movie, become normal foreign keys.

So your schema should look like this: schema

And yes, like you had to do in your example you now have to create a model to represent that table, and the reason is the stated adobe, it is now an entity itself.

#6006 report it
Say_Ten at 2011/12/05 09:39am
This is correct...

Don't get hung up on the fact that the middle entity joins the two other entities, as it has properties in its own right. This make is an entity with two HAS_MANY relationships and should be modelled in this way. It's called an Association Class and there's lots of hits on Google for it but this one looked half decent:

etutorials.org Association Class

Leave a comment

Please to leave your comment.

Write new article