BelongsToMany and wanted duplicates

It would be similar to a pivot table but I wouldn't actually call it that. I'd call it a Roles table and use an auto-increment and include my extra fields and my foreign keys. Three tables: Movies, Roles, People.

Table Movies
  increment id;
  string    title;
  string    summary;
  integer   rating;
  timestamp release_date;

Table People
  increment id;
  string    name;
  timestamp date;
  

Table Roles
  increment id
  integer   movie_id
  integer   person_id
  string    position;   // Director, Producer, Actor, Crew, Etc...
  string    character_name default '';
  foreign   movie_id -> movie.id
  foreign   person_id -> people.id

Your Movies would have many Roles. Your People would have many Roles. Each Role belongs to a Person. Each Role belongs to a Movie. This will probably require an extra query to retrieve the People on the Movie's Show view. Or Movies on a Person's Show view.

You should be able to use a HasManyThrough Relationship.

http://laravel.com/docs/5.1/eloquent-relationships#has-many-through

class Movie extends Model
{
    /**
     * Get all of the posts for the country.
     */
    public function people()
    {
        return $this->hasManyThrough('App\Person', 'App\Role');
    }
}

Perfect case for polymorphic many to many imo.

@Mushr00m You just can't do it with sync. You need attach.

Note: don't use sync for updating in this case either - it will affect all the pivot entries for given actor-movie pair.

Nice @JarekTkaczyk.

The sync method behind the scenes loops through and detaches everything associated then loops through the given array of ids and attaches those. Attach if you want to add to. Detach with an array to detach only certain ones for detach() no data to remove all associations.

@jekinney Yes, it does by default. However, you can use 2nd param $detach = false and then it only inserts and/or updates records, without detaching all the others.

https://github.com/laravel/docs/pull/1667/files

Thanks to all for your answers. I ended up doing @jekinney said and just detach all the pivot elements and then loop through my array of new elements and attach each one.

It sounds like you're facing a situation where you want to store multiple entries in the pivot table for the same actor and movie combination, each with a different role. While the traditional belongsToMany relationship in Laravel uses composite primary keys to uniquely identify records in the pivot table, you indeed need to allow duplicates to handle multiple roles for the same actor and movie pair.

To achieve this, you can't directly use the sync() method provided by Laravel's Eloquent. Instead, you'll need to manually insert records into the pivot table. Here's how you can approach this:

// Assuming $movieId is the ID of the movie and $actorId is the ID of the actor.

$roles = ['role1', 'role2', 'role3']; // An array of roles for the actor in the movie.

// Loop through the roles and insert records into the pivot table. foreach ($roles as $role) { DB::table('actor_movie')->insert([ 'movie_id' => $movieId, 'actor_id' => $actorId, 'role' => $role, // Other pivot table columns... ]); }

In this example, you're manually inserting records into the pivot table for each role associated with the actor and movie. This allows you to have duplicates in the pivot table for the same actor and movie pair.

Remember to adjust the code according to your specific setup and database structure. Make sure you're using the appropriate table and column names.

While this solution provides the desired outcome, keep in mind that it bypasses some of the conveniences of Eloquent's built-in methods. However, it gives you more control over the data manipulation process in cases where you need to handle complex scenarios like the one you described.......https://www.ytvancedpro.com/

Thanks!!