Complex many to many

From ClassDBI

CdbiCookbook tips on many-to-many relationships.

---

Before you try and implement the following, consider having a look at Class::DBI::Relationship::HasManyOrdered; it provides most of the features you're likely to need "out of the box"

---

When you Store Info in your Mapping Table

Many-to-many relationships aren't always as simple as "one table maps to another with a mapping table that has two columns, one of the primary keys of each table we're mapping." Sometimes the mapping table has useful information in it. Below we'll discuss an example.

Given three tables (explained in a simplified SQL style). In this example we have a database of stories that can reference any number of images. Different stories can reference the same image, and that gives us the many-to-many relationship. The twist is that the mapping table contains story priority information for the images: which images are more important to the story than others.

 CREATE TABLE stories (
   id int primary key,
   title varchar,
   author varchar,
   content text
 );

 CREATE TABLE images (
   id int primary key,
   file_name varchar,
   caption varchar
 );

 CREATE TABLE story_images (
   id int primary key,
   story int,
   image int,
   priority int
 );

Class::DBI has a great mapping feature which you'd be tempted to use here, but that isn't such a good idea. Here, again, are the relationships we need to express.

• Stories have many images.
• Images have many stories.

We have to do that without losing the priority information. You can't use Class::DBI's mapping feature because it will lose any extra information you would have needed in your mapping table. So here is a way to do that.

First we tell the Story class that it has many StoryImages which are sorted based on priority.

 Story->has_many(images => StoryImages => {sort => 'priority'});

Now we can tell StoryImages that each object has an Image.

 StoryImages->has_a(image => 'Image');

Now every story references their images.

 my $story = Story->retrieve($id);
 foreach my $image ( $story->images ) {
   printf "%d -> %s [%s]", $image->priority,
     $image->image->file_name, $image->image->caption;
 }

That isn't very pretty, though, because you still have to reach down the object hierarchy to get image information. At this point you might be tempted to use Class::DBI's might_have() feature, but no go. It relies on the primary key of your class and the class you might_have() to be identical. The mapping table won't provide that requirement. We could do this manually, however, in our StoryImages class.

 sub file_name { shift->image->file_name(@_) }
 sub caption    { shift->image->caption(@_) }

Now our printf can look something like this.

 printf "%d -> %s", $image->priority, $image->caption;

But what happens if we want to update the image caption?

 my $image = Story->retrieve($id)->images->first;
 $image->caption("A pretty devil.");
 $image->update;

That doesn't do what you expect. It will update the StoryImages object, but not the Image object. You'd have to call update() on that object.

 $image->caption("A pretty devil.");
 $image->image->update;

That's ugly again! We can fix this by using a trigger for after_update in our StoryImages class.

 StoryImages->add_trigger(after_update => sub {shift->image->update});

If you want to be able to call discard_changes, you'll have to override the default method with one like this:

 sub discard_changes {
     my $self = shift;
     $self->SUPER::discard_changes($self); # discard StoryImages changes
     $self->image->discard_changes(); # discard Image changes
 }

Now calling update on the StoryImages object will work correctly. What if you want to add a new image? Clearly this would be ideal.

 $story->add_to_images({
   file_name => "pretty.jpg",
   caption => "A pretty angel.",
   priority => 100, # low
 });

Once again we're disappointed. So we should add a before_create trigger to the StoryImages class.

 StoryImages->add_trigger(before_create => sub {
   my ($data) = @_;
   $data->{image} = Image->create({
     file_name => delete($data->{file_name}),
     caption => delete($data->{caption}),
   });
 });

We also need to tell StoryImages that it can store information for the file_name and caption columns, or it will reject them before the trigger gets called, and an exception will be thrown.

 StoryImages->columns( TEMP => qw(file_name caption) );

Now adding images to the database will be less painful.

Lets discuss our mapping from images to stories, which will be much less complex because that relationship is more useful for inspecting images than actually using them. Because this relationship would be used much less often, We're not going to setup all the conveniences that going in the other direction. If all we have on hand is an image object we can find the stories it belongs to easily. And this time we can use Class::DBI's mapping feature.

 Image->has_many(stories => [StoryImages => 'story']);

We'll also tell the StoryImages class that it can implement the stories() method, and that it can relate to stories, to make access a little easier.

 StoryImages->has_a( story => 'Story' );
 sub stories   { shift->image->stories(@_) }

And to get a list of story titles this image is referenced by.

 printf "%s\n", $_->title foreach $image->stories;

There you have it.

You can also look at ComplexManyToManyExample for an attempted implementation of what's described on this page. Good Luck!