Re: Duplicate constraint on many-to-many

[prev] [thread] [next] [Date index for 2005/06/25]

Bill Moseley wrote:
> If I have a many-to-many setup (Person, Role, PersonRole link table):
> 
>     Person->has_many( roles => [ PersonRole => 'role' ] );
>     Role->has_many( people => [ PersonRole => 'person' ] );
> 
> I can make a $person and 'Admin':
> 
>     $person->add_to_roles( {
>         role    => Role->find_or_create( { name => 'Admin' } )
>     } );
> 
> If this gets called again on the same $person I'd like it to quietly
> fail -- that is, not add in a duplicate relationship in the link
> table.

You could do probably just do something like this:

  my $role = Role->find_or_create({ name => 'Admin' });
  my $pr = PersonRole->find_or_create({
    role   => $role,
    person => $person,
  );

Or am I missing something?

> I can (and do) add a CONSTRAINT in the database:
> 
>     create table person_role (
>         id      SERIAL PRIMARY KEY,
>         role    integer NOT NULL REFERENCES role,
>         person  integer NOT NULL REFERENCES person,
>         CONSTRAINT key UNIQUE (role, person)
>     );
> 
> but then I need to trap the error.  Saying someone is an Admin when
> they already are an admin isn't really an error.
> 
> I thought I might be able to use $PersonRole->add_constraint and use
> $changing to search for an existing record. But $changing looks
> different when $role->add_to_people is called than it does when
> $person->add_to_roles is called.  I suppose I can just test for both
> cases.
> 
> Must be a common issue, so is there a standard way to test for an
> existing relationship?

find_or_create() is usually enough for me.

> Make a new method that does this?
> 
>     $person->add_to_roles( { role = $role } )
>         unless grep { $role = $_ } $person->roles;

That looks good to me. Why not just use that in your base class and
override add_to_roles()?

        -- 
        Michael Peters
Developer
Plus Three, LP