Re: Many-to-Many with just on table plus linking table

[prev] [thread] [next] [Date index for 2005/04/04]

Brett Sanger wrote:
> I decided to finally look into Class::DBI, and I'm already quite lost.  The
> documentation is a bit spotty and chaotically mixes low-level and high-level
> bits.  I'll happily offer some patches, but first I need to figure out what's
> going on myself.  (The wiki did not offer clarification on this matter)

It does take _some_ reading to get up to speed, but it isn't that hard really. I found the docs, in combination with the talks and articles 
referenced on the wiki, to be enough to get working knowledge. It all feels so much second nature now, that I never want to go back to plain 
DBI.

> #########################################
> package Forum::Topic;
> use base 'Forum::DBI';
> 
> Forum::Topic->table('forum_relations');
> Forum::Topic->columns(Primary => qw/message topic/);
> Forum::Topic->has_a(message => 'Forum::Message');
> Forum::Topic->has_a(topic => 'Forum::Message');

This class name seems wrong to me: it doesn't model a topic, it models forum threads.
Are you sure Topics and Messages are the same thing? Why would one message have multiple topics? Does it really get posted to multiple threads?

I think it would help you a great deal to think in terms of real objects and their relations. A class layout like the following would, IMHO, 
make more sense:

Forum
	has_many Forum::Topics
Forum::Topic
	has many Forum::Messages
Forum::Message
	has a Forum::Topic


> It turns out that add_to_topics() wants a 'message' value passed, and
> add_to_messages wants a 'topic' value passed, with the result of switching the
> meanings of my columns.  If I try to be explicit and list both values, it
> ignores what I passed.

That is how the has_many relationship works: one object has a collection of other objects.
If you want to be really explicit, you would say:

Forum::Topic->create({topic => $topic, message => $message});

> add_to_* is only barely defined in the documentation, so I've been unsuccessful
> in determing what I've done wrong.  Given that the code runs if I meet the
> expectations, that makes it out to be a logical error on my part, but I can't
> see how my sample is significantly different from the Film/Actor/Role example
> in the docs.  

It's significantly different in that you complicate matters by forcing two different types of objects into the same class, and force them to 
use the same mapping table.
The documentation is adequate: add_to_messages takes a hashref of keys and values defining the new message, with the foreign key of the 
object being set automatically.

I have many instances with many-to-many relationships that work perfectly fine. Let me give you an example from a small CMS I wrote. It has 
Pages, and Photos. Each page can have many photos attached to it, and each photo can appear in many pages. I'll leave the table definitions 
out of it, they can easily be deduced from the classes. I've also left out as many details as possible.

This is the linking table:

    package Page_Photo;
    __PACKAGE__->columns(Primary    => qw/page_id photo_id/);
    __PACKAGE__->has_a(page_id      => 'Page');
    __PACKAGE__->has_a(photo_id     => 'Photo');

The page table:

    package Page;
    __PACKAGE__->table('page');
    __PACKAGE__->columns(Primary    => qw/page_id/);
    __PACKAGE__->columns(Essential  => qw/title summary body coverdate/);
    __PACKAGE__->has_many( photos   => ['Page_Photo' => 'photo_id']);

The photo table:

    package Photo;
    __PACKAGE__->table('photo');
    __PACKAGE__->columns(Primary    => qw/photo_id/);
    __PACKAGE__->columns(Essential  => qw/caption description url/);
    __PACKAGE__->has_many( pages    => ['Page_Photo' => 'page_id']);


This all works perfectly:

    # add photo to page
    $page->add_to_photos({ caption => 'Adams in Oxford', description => '...', url => '...'});

    # get pages where this photo appears
    @pages = $photo->pages();

Hope this helps a bit!

Rhesa