Selecting join table with mant to many?

Yii 1.1 General Discussion
41

Relying on BELONGS_TO relations after querying ‘with-through’ turned out to be a bad idea. These properties aren’t cached during the query, so every access means additional query for every row in the relations table which is unacceptable.

My solution was to add ‘index’ property to the first relation of the first table, set to the field which is a foreign key of the second table, so that rows queried from the relations table are indexed by IDs of the second table (as rows from the relations table and second table are one-to-one in this query). This way, after the request, I can iterate through rows of the second table and get corresponding rows from the relations table.




[ First.php ]


    public function relations()

    {

        return array(

            'rels' => array(

                self::HAS_MANY, 'Rel.php', 'firstId',

                'index' => 'secondId'),

            'seconds' => array(

                self::HAS_MANY, 'Second.php', 'secondId',

                'through' => 'rels'),

        );

    }


    public function getWithSeconds($id)

    {

        $first = $this->findByPk($id, array('with' => array('rels', 'seconds')));

        foreach ($first->seconds as $second)

            $second->propertyFromRel = $first->rels[$second->id]->property;

        return $first;

    }
42

I wrote an extension to get data from a join table into properties of the related objects:

  • Forum thread

  • GitHub repository

When loading a related object through a join table, I want to be able to load (eager or lazy) data from the join table into properties of the related objects.


class Viewer extends CActiveRecord {


  public function relations() {

    return array(

      'movies' => array(self::MANY_MANY, 'Movie',

        'viewer_watched_movie(viewer_id, movie_id)',

        'select' => 'viewer_watched_movie.liked',

      ),

    );

  }


  // An example of lazy loading the liked property into Movie objects 

  public function viewerReviews($id) {

    $viewer = Viewer::model()->findByPk($id);

    foreach ($viewer->movies as $m)

      echo $viewer->name . ($m->liked ? ' liked ' : ' didn’t like ') . $m->title;

  }


}

// and perhaps we need to declare liked as a property of Movie

The extension does this (with slightly different relation specs) but I think something like the above should be part of the framework. There’s nothing exotic about data that logically belongs to a relation between objects rather than to the objects. In SQL RDBMS they are conventionally put in a join table. It should be easy to access the data.

43

fsb

For what? Framework already have this feature:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through

44

I tried applying that to my problem and failed. I must have gone through that section of the guide half a dozen times. I’d be grateful if you could help me get the relation definitions right.

If, in the above example if I redefine the relations in Viewer thus:


class Viewer extends CActiveRecord {


  public function relations() {

    return array(

      'moviesJoin' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),

      'movies' => array(self::MANY_MANY, 'Movie', 'viewer_watched_movie(viewer_id, movie_id)',

        'through' => 'moviesJoin',

      ),

    );

  }


  // An example of lazy loading the liked property into Movie objects 

  public function viewerReviews($id) {

    $viewer = Viewer::model()->findByPk($id);

    foreach ($viewer->movies as $m)

      echo $viewer->name . ($m->liked ? ' liked ' : ' didn’t like ') . $m->title;

  }


}

Then I get CException: Property "Movie.liked" is not defined.

If I try to specify that the liked column should be loaded:


      'movies' => array(self::MANY_MANY, 'Movie', 'viewer_watched_movie(viewer_id, movie_id)',

        'through' => 'moviesJoin', 'select' => 'viewer_watched_movie.liked',

      )

Then the error is CDbException: Active record “Movie” is trying to select an invalid column “viewer_watched_movie.liked”. The following ‘select’ specs all throw the same exception.


'select' => 'liked'

'select' => 'moviesJoin.liked'

'select' => 'moviesJoin_moviesJoin.liked'

Putting ‘select’ options in the ‘moviesJoin’ relation causes a PHP error: array_diff(): Argument #1 is not an array CActiveRecord.php:1969

Full context of this code is in the GitHub repo I previously mentioned, including EER chart, fixtures and unit test.

45

fsb

There is no ‘through’ option for MANY_MANY. ‘Through’ is HAS_MANY/HAS_ONE option for replace MANY_MANY in situations when you need to access to fields in joining table.

First. You need model for ‘viewer_watched_movie’ table. Than you can use ‘through’:




public function relations() {

    return array(

      'moviesJoin' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),

      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'moviesJoin'),

);

Note that FK format for last relation(movies) definition changed if you use 1.1.9-dev.

46

OK.

Now, how do I access the value of the liked column in the join table?

Assuming


$viewer = Viewer::model()->findByPk($id);

$movie = $viewer->movies[0];

how do I tell if $viewer liked $movie?

47

Simple




$moviesJoin = $viewer->moviesJoin[0];

For max effective you can do that:




$viewer = Viewer::model()->with(array('movies','moviesJoin'))->findByPk($id);
48

How do I know that $viewer->moviesJoin[0] corresponds to $viewer->movie[0] ? I considered this but didn’t dare assume that the indexes to these arrays have semantic value.

49

I wonder if you could not take another approach instead. Say we have 3 models, Viewer, Movie and Loan (which represents the link table)




// in Viewer

'loans' =>array(self::HAS_MANY,'Loan','id_viewer'),


// in Loan (which is the link table)

'viewer'=>array(self::BELONGS_TO,'Viewer'),

'movie'=>array(self::BELONGS_TO,'Movie'),


// Then use a criteria / provider like this:

$criteria=new CDbCritera(array(

    'with'=>'loans.movie',

));

$provider=new CActiveDataProvider('Viewer',array(

    'criteria'=>$critieria,

));


// You then can access the data in a list view like:

foreach($data->loan as $loan)

{

    echo "Movie: ". $loan->movie->name;

    echo $loan->active ? ' (active)' : ' (not active)';

}
50

My example had viewed as the join table with column liked. I think your loan is equivalent.

It works for part of the problem. When filling a zii.CGridView display of viewers you might want all viewers, not just those that appear in the join table.

But at least it doesn’t use the undocumented feature that two independently-gotten arrays of AR objects are index aligned. Creocoder’s suggestion is especially worrying since he hinted this stuff may be changing in 1.1.9.

So far I still prefer:


$viewers = Viewer::model()->with('watched.movie')->findAll();

foreach ($viewers as $viewer) 

  foreach ($viewer->_moviesJoin as $j) 

    echo $j->viewer->name . ($j->liked ? ' liked ' : ' didn’t like ') . $j->movie->title . PHP_EOL;

Which is what my CActiveRecord extension uses. It also offers a lazy version. And allows access in the AR idiom, e.g.:


foreach ($viewers as $viewer) 

  foreach ($viewer->movies as $movie) 

    echo $viewer->name . ($movie->liked ? ' liked ' : ' didn’t like ') . $movie->title . PHP_EOL;
51

I can’t follow - you do get all viewers and you join all loans/viewed including movies. If a viewer has not viewed a movie, then the relation will be an empty array.

It is documented - but maybe the docs could be enhanced with better examples.

I can’t really see the difference. You select all viewers and join all watched.movies. Why would you need an extension for this?

52

You’re quite right. I misunderstood your example. Your method is the same as the one I put in my CActiveRecord extension.

What I think lacks documentation is as follows.

If Viewer has:


public function relations() {

    return array(

      'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id'),

      'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'watched'),

);

then I think creocoder is saying we can access:


$viewer->watched[3]->liked;

$viewer->movies[3]->title;

and know that these two data correspond to the same movie.

53

Hi thefsb, I read your wiki article about this topic too, which made me think…

To get rid of this ‘indexing trick’, we could do the following:




'watched' => array(self::HAS_MANY, 'ViewerWatchedMovie', 'viewer_id', 'index'=>'movie_id'),

'movies' => array(self::HAS_MANY, 'Movie', 'movie_id', 'through'=>'watched', 'index'=>'id'),

This way we make sure that the ‘watched’ entries, as well as the ‘movies’ entries are both indexed with the id of the movie.

Then we can use them like this (tested, works):




foreach($viewer->movies as $id=>$movie) {

	echo $viewer->name . 'watched ' . $movie->title . 'and ' . ($viewer->watched[$id]->liked ? ' liked ' : ' didn’t like ') . ' it.';

}

Best regards…

54

This is not necessary. $viewer->moviesJoin[0] corresponds to $viewer->movie[0] as $viewer->moviesJoin[n] corresponds to $viewer->movie[n].

You need to be more decisive and everything will be okay. :)