Re: Cross between MightHave and HasMany?

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

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' ]);

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)

Kings

 > 
 > Also I'd like to be able to remove the relationship, without deleting  
 > either the person or the category themselves.
 > 
 > Any ideas?
 > 
 > Jess
 > 
 > -- 
 > Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/