How do I get values from the link table in DBIx::Class?

Question

In my app I have 2 table, books and tags, and the link table book_tags. The link table also contains the number of times the book was tagged with this particular tag. I can add a tag by doing

$book->add_tag($tag, { tag_count => 10 });

However when I retrieve the tags for a book

@tags = $book->tags();

it does not seem to return the values from the link table.

Is there a way to get the values from the link table without doing it by hand?

Answer 1

Since you have a join table named book_tags, you will have to create a many_to_many relationship from the books table to the has_many relation of the books table itself. The has_many relation should retrieve the ids of the tags associated with the book from the table book_tags. You may have to add relationships similar to:

In Your::App::Schema::Result::Book:

__PACKAGE__->has_many( book_tags => 'Your::App::Schema::Result::BookTag', 'book_id' );

__PACKAGE__->many_to_many( tags => 'book_tags', 'tag' );

In Your::App::Schema::Result::BookTag:

__PACKAGE__->belongs_to( tag => 'Your::App::Schema::Result::Tag', 'tag_id' );

Answer 2

Thanks! I have already done this part, and it works.

My tables look like this:

BOOK:

book_id book_title [other fields]

TAG:

tag_id tag_name [other fields]

BOOK_TAG:

book_id tag_id tag_count

So I have a many-to-many relationship between tags and books with an additional attribute, tag_count, which is stored in the link table.

I can add a tag to a book by doing

$book->add_to_tags($tag, { tag_count => 10 } );

which populates the tag_count field in the BOOK_TAG table.

But when I do

$book->tags();

it does not automatically retrieve tag_count field from BOOK_TAG. I can write

$tag = $schema->resultset('BookTag')->find( { book_id=>$book->book_id, tag=>$tag->tag_id });
$tag_count = $tag->tag_count();

I'm trying to see if there is an easier way to get the extra attribute from the link table.

Answer 3

You can proxy properties through relationships like this:

Schema::Tag->has_many('book_tags' => 'Schema::BookTag',  
  undef, {  
    proxy => [ 'tag_count' ],  
  }  
);

Then you can access 'tag_count' like this:

my $tag_rs = $book->tags;
for( my $tag = $tag_rs->next ){
  print $tag->tag_count;
}

You can find more at the relationship docs.