Re: Cross between MightHave and HasMany?

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

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 ,)

> 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.

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


Jess