Re: Cross between MightHave and HasMany?

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

--- Jess Robinson <castaway@xxxxxxxxxxxxx.x.xxxx.xx> wrote:

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

Since a person has at most only one category, Why do you not want to store
category_id  in person?  

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

But if you want , Have you tried making a Temp column of person category? I
haven't tried Temp but from reading the docs, i hope to be able to say:

Person->columns( Temp => [ "category"]);
Person->has_a(category => "PersonCategory");

.... then  have everything work just like if the category column was in the
person table.  It should be able to update the PersonCategory object.

prsn_obj->category->cat_id($cgi_val);
prsn_obj->update; # or maybe prsn_obj->category->update;

also

prsn_obj->category->delete;


Also, if you don't know, CDBI::Relationship docs tell how to roll your own
relationship types.

> Jess
> 


=====
pjs


		
__________________________________ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com