How Can I Retrieve All Collaborators That Are Directors On A Specific Movie (Laravel + Eloquent)?

- 1 answer

I have 3 tables: movies, movies_collaborators and movie_collaborator_types.

A movie can have many movie_collaborators that can have many movie_collaborator_types.

How can I define and retrieve a collaborator that is a Director on a Movie on this type of relation?

It would be easy if it was a simple Many to Many relation, just creating a table collaborator_movie with movie_id and collaborator_id fields, but in this case I have another guy in the middle, the collaborator_type and I need to get and set this relation based on this field.

How can I define this relation?

Obs: I'm thinking about create a table collaborator_movie with movie_id and collaborator_id and collaborator_type_id. But I'm stuck there since it's the first time I make a Many to Many relation with 3 fields on the intermediate table.



You could constraint your results with a pivot condition:

/** YourController.php */

$title = 'Inception'; // example of movie title
$type = 1 // assuming id=1 is for a 'director' type

$colaborators = Movie::where('title', $title) // searching the movie
                    ->first() // getting the movie object
                    ->collaborators() // calling the relationship
                    ->wherePivot('collaborator_type_id', $type) // filtering <---
                    ->get(); // getting the results

I haven't test it myself but this should work. Check the documentation regarding this aspect.

PS: If I'm not wrong, for this to work you should have this extra pivot column defined in your relationship model:

/** Movie.php */

public function collaborators()
    return $this->belongsToMany(Collaborator::class)->withPivot('collaborator_type_id');