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 ¶

~~~ [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)`]

### 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.