Difference between #5 and #6 of Accessing data in a join table with the related models

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](http://i.imgur.com/H2Q34.png "EER diagram")
~~~
[sql]
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 `Movie`s he or she watched with
this relation:
~~~
[php]
class Viewer extends CActiveRecord {
    ...
    public function relations()     {
        return array(
            'movies' => 'movies' -> array(self::MANY_MANY, 'Movie', 
                'viewer_watched_movie(movie_id, movie_id)'),
    ...
~~~

Using it like this:
~~~
[php]
$viewer = Viewer::model()->findByPk($id);
    foreach ($viewer->movies as $movie)
        echo $viewer . ' watched ' . $movie->title; 
~~~
[This will perform poorly because it lazily loads `Movie`s one as a time in the
loop. I can pep it up thus:
`Viewer::model()->with('movies')->findByPk($id)`]with `with()`:
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?:

~~~
[php]
$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.
~~~
[php]
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' => 'moviesJoin'),
    ...
~~~
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:
~~~
[php]
$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](http://www.yiiframework.com/forum/index.php?/topic/8581-selecting-join-table-with-mant-to-many/page__st__40__p__123710__hl__creocoder#entry123710
"forum link") that the coherence of indexes of the two arrays
`$viewer->moviesJoin` 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:
~~~
[php]
class ViewerWatchedMovie extends CActiveRecord {
    ...
    public function relations() {
        return array(
            'movie' => array(self::HAS_ONE, 'Movie', 'movie_id'),
    ...
~~~

And that allows:
~~~
[php]
$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.