[Dbix-class] Updating many-to-many relationships

[David Cantrell]

I have a bunch of classes set up as part of the ACL system for this 'ere
application what I'm writing.  There is a 'user' table, a 'role' table,
and sitting in between them a 'user_role' table.  Users can have any
arbitrary combination of roles, so this is yer classic many-to-many
situation. Pared down to the minimum, the classes are:

  package Database::Result::User;
  use base 'DBIx::Class::Core';
  __PACKAGE__->table('user');
  __PACKAGE__->add_columns(
    id       => { data_type => 'INT',  is_nullable => 0 }, # autoinc
    status   => { data_type => 'CHAR', is_nullable => 0 },
    username => { data_type => 'CHAR', is_nullable => 0 }
  );
  __PACKAGE__->set_primary_key( "id" );
  __PACKAGE__->has_many( "user_roles", "Database::Result::UserRole", { 'foreign.user_id' => "self.id" } );
  1;

  package Database::Result::UserRole;
  use base 'DBIx::Class::Core';
  __PACKAGE__->table('user_role');
  __PACKAGE__->add_columns(
    user_id => { data_type => 'INT', is_nullable => 0 },
    role_id => { data_type => 'INT', is_nullable => 0 },
  );
  __PACKAGE__->set_primary_key( qw(user_id role_id) );
  __PACKAGE__->belongs_to("user", "Database::Result::User", { 'foreign.id' => 'self.user_id' });
  __PACKAGE__->belongs_to("role", "Database::Result::Role", { 'foreign.id' => 'self.role_id' });
  1;

  package Database::Result::Role;
  use base 'DBIx::Class::Core';
  __PACKAGE__->table('role');
  __PACKAGE__->add_columns(
    id   => { data_type => 'INT',  is_nullable => 0 }, # autoinc
    name => { data_type => 'CHAR', is_nullable => 0 },
  );
  __PACKAGE__->set_primary_key( "id" );
  __PACKAGE__->has_many( "user_roles", "Database::Result::UserRole", { 'foreign.role_id' => "self.id" } );
  1;

I can create users with roles easily, creating roles on the fly if
necessary:

  my $person = ...->create({
    username   => 'anselm',
    status     => 'alive',
    user_roles => [
      { role => { name => 'Monk' } },
      { role => { name => 'Archbishop' } },
    ]
  });

I couldn't see this documented anywhere, but found it in the mailing
list archives.  It creates any necessary entries in the 'role' table,
and in the 'user_role' table.

However, I can't find any nice way of updating a user's
roles, something like this (which I tried, but it doesn't work) ...

  $person->status('dead');
  $person->user_roles([
    { role => { name => 'Saint' } }
  ]);
  $person->update();

Am I missing something?  It would make my life ever so pleasant if there
were a nice easy way of doing this.  As it is, however, the call to the
user_roles() method is not having any effect at all - there's no queries
generated whatsoever for it, just the UPDATE to change the status from
alive to dead.

-- 
David Cantrell | even more awesome than a panda-fur coat

There is no one true indentation style,
But if there were K&R would be Its Prophets.
Peace be upon Their Holy Beards.