Accessing data in a join table with the related models

You are viewing revision #7 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version or see the changes made in this revision.

« previous (#6)next (#8) »

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 ~~~ [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' => '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:

```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->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:

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

16 1
30 followers
Viewed: 126 533 times
Version: Unknown (update)
Category: How-tos
Written by: fsb
Last updated by: krowe
Created on: Dec 5, 2011
Last updated: 6 years ago
Update Article

Revisions

View all history

Related Articles