Accessing data in a join table with the related models Follow @yiiframework

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

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.

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;

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') . '!';

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

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:

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.

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