OctoberCMS: Three Way Complex Relationship

Question

I'm trying to create a three-way relationship with a pivot table.

What I have 4 models; Movie, People, PeopleRole, and MovieCast.

I currently have a DB schema like so:

movies_

-------------------
| id | name       |
|----|------------|
| 1  | Fight Club |
| 2  | Inception  |

movies_peoples

-------------------
| id | name       |
|----|------------|
| 1  | Brad Pitt  |
| 2  | Tom Cruise |

movies_peoples_roles

--------------------
| id | name       |
|----|------------|
| 1  | Director   |
| 2  | Writers    |

movies_casts (pivot table)

--------------------------------------------------
| (pk) movie_id | (pk) role_id |  (pk) people_id |
|---------------|--------------|-----------------|
| 1             | 1            | 1               |
| 1             | 1            | 2               |
| 1             | 2            | 1               |
| 2             | 1            | 2               |

Currently I have no idea how to do this. What is the best solution to implement this kind of relation?

Answer 1

I assume, you want the following,

For movies you want the list of casted actors and roles.

For actors, you want the list of movies and roles

For roles, you may want the list of actors and movies

So in my model MovieCast i would add a belongsTo() relation to each of those models, since this is very easy and understandable, i won't add an example, unless you ask for it in the comments.

Then you should add this, since it doesn't have a primary key:

/**
 * primaryKey 
 * 
 * @var integer
 * @access protected
 */
protected $primaryKey = null;
/**
 * Indicates if the IDs are auto-incrementing.
 *
 * @var bool
 */
public $incrementing = false;

Then in each of my other models, i would add a relationship with the MovieCast model, something like:

Movie model

public function casting(){

    return hasMany('\App\MovieCast', 'movie_id', 'id');

}

Actor model

public function appears_on(){

    return hasMany('\App\MovieCast', 'people_id', 'id');

}

Roles Model

// Please replace my ridiculous name with something else 
public function actors_and_movies_with_this_role(){ 

    return hasMany('\App\MovieCast', 'role_id', 'id');

}

Then when you access for example an actor, you can use something like:

foreach($actor->appears_on as $appearance){
   echo $appearance->movie->name;
   echo $appearance->role->name;
}