Re: Cross between MightHave and HasMany?

[prev] [thread] [next] [Date index for 2004/11/02]

Jess Robinson writes:
 > On Tue, 2 Nov 2004 15:08:47 -0500, Kingsley Kerce  
 > <kingsley@xxxxxxxxxxxxxxxx.xxx> wrote:
 > 
 > > Jess Robinson writes:
 > >  > Hi folks,
 > >  >
 > >  > I have a setup similar to:
 > >  >
 > >  > create table person (
 > >  >     personid int primary key auto_increment,
 > >  >     firstname varchar(32),
 > >  >     initials varchar(16),
 > >  >     surname varchar(64),
 > >  >     date_of_birth datetime
 > >  >   );
 > >  >
 > >  > create table categories (
 > >  >     catid int primary key auto_increment,
 > >  >     category varchar(128)
 > >  >   );
 > >  >
 > >  > create table personcategories (
 > >  >      personid int,
 > >  >      catid int
 > >  > );
 > >  >
 > >  > So I've been looking for a way to say 'person might have a category,  
 > > via
 > >  > table personcategories', I can't see way to do it using existing
 > >  > relationship types, or am I missing something? Essentially I'd like
 > >  > $person->category() and $person->category($catobj) to do the right  
 > > thing.
 > >
 > > It seems as if a many-to-many would serve you fine.
 > > First, rename your last two tables to 'category' and 'personcategory'.
 > > Then set up a many-to-many as follows (code untested).
 > >   PersonCategory->has_a(personid => 'Person');
 > >   PersonCategory->has_a(catid => 'Category');
 > >   Person->has_many(categorys => [ 'PersonCategory' => 'catid' ]);
 > >   Category->has_many(persons => [ 'PersonCategory' => 'personid' ]);
 > 
 > This is actually what Im currently doing, its just slightly annoying to  
 > have to remember that it will return a list, and I need to grab the first  
 > item from that list. Since a person can only be in one category at once.  
 > (At least in my scenario ,)

To abstract away the annoyance, put a category() method in the Person
package:

package Person;
...
sub category {
  my $self = shift;

  my ($category) = $self->categorys;
  return $category;
}

 > > Now you can use
 > >
 > >   my $person = Person->retrieve(1);
 > >   foreach my $category ($person->categorys) {
 > >     ...
 > >   }
 > >   # or if in your scenario you know a person will only be in one category
 > >   my ($category) = $person->categorys;
 > >   ...
 > >
 > >   # your "might have" requirement is satisfied, as $person->categorys
 > >   # will yield nothing if a person has no category
 > 
 > > Not sure what you're after when you say: $person->category($catobj)
 > 
 > To be able to pass in a category object to $person->category and have it  
 > update PersonCategories as appropriate.. At the moment I'm first deleting  
 > any rows in there that contain that person_id, and then adding the new one.

That's what the CDBI add_to_*() methods are for.

$person->add_to_categorys({
  catid => $catobj->id,
});

The above will insert a row into PersonCategory.

Alternatively, I suppose you could adjust the above category() method,
as follows, but add_to_categorys() seems just fine.

sub category {
  my $self = shift;
  my $catobj = shift;

  if ($catobj) {
    # do the update here to your liking...
  }

  my ($category) = $self->categorys;
  return $category;
}

Kings

 > >
 > >  >
 > >  > Also I'd like to be able to remove the relationship, without deleting
 > >  > either the person or the category themselves.
 > >  >
 > >  > Any ideas?
 > >  >
 > 
 > 
 > Jess