LunarCowgirl has asked for the wisdom of the Perl Monks concerning the following question:

I'm sure the answer is probably simple, but I haven't yet stumbled onto it. I've been learning DBIx::Class with Catalyst. So far I've been able to figure out how they work together, but I'm stuck on one issue.

I have a many-to-many relationship that joins together three tables which are basically called "page", "chapter", and "book". I can gather resultsets and pass them into Template Toolkit, using them as such:

[% page.book.title %]: [% page.chapter.num %]

which prints out the title and chapter number of the book. My questions is: how can I do that within Catalyst itself? I'm setting up an RSS feed, and I need access to "page.book.num" and "page.chapter.num" to create a url within the feed. I've tried both variations below, as well as others:

# Gets latest 10 results
my $rs = $c->model('DB::Page')->get_rss;

while ( my $page = $rs->next ) {
    my $book    = sprintf( "%02d", $page->book->{num} );
    my $chapter = sprintf( "%02d", $page->chapter->num );
    ...
}

[download]

Neither of those work. The first returns "00". The second produces an error that "num" can't be found. Is there any simple way to do this?

Replies are listed 'Best First'.
Re: Accessing many-to-many relationships in Catalyst
by Your Mother (Archbishop) on Aug 24, 2014 at 02:06 UTC

    We would have to see your relationships to help, I think. Also this does not sound like a many_to_many situation to me. A book has_many chapters and has_many pages, as does the chapter. They each belong_to a chapter and a book. Many pages are not in many books.

    Pseudo-ish code, three Result files: Book, Chapter, Page

    package Book;
use parent "DBIx::Class::Core";

__PACKAGE__->table("book");
__PACKAGE__->add_columns(qw/ id title /);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many( chapters => "Chapter",
                       { "foreign.book" => "self.id" } );
# Probably a belongs_to Author here...
1;

    [download]
    package Chapter;
use parent "DBIx::Class::Core";

__PACKAGE__->table("chapter");
__PACKAGE__->add_columns(qw/ id title num book /);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many( pages => "Page",
                       { "foreign.chapter" => "self.id" } );
__PACKAGE__->belongs_to( book => "Book" );
1;

    [download]
    package Page;
use parent "DBIx::Class::Core";

__PACKAGE__->table("page");
__PACKAGE__->add_columns(qw/ id content chapter num /);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->belongs_to( book => "Chapter" );
1;

    [download]

    Then for TT2, [% page.chapter.book.title %]: [% page.chapter.num %]. And–

    # Gets latest 10 results
while ( my $page = $rs->next ) {
    my $book    = sprintf( "%02d", $page->chapter->book->title ); # Bo
+ok number???
    my $chapter = sprintf( "%02d", $page->chapter->num );
}

    [download]

    Note, I feel "position" or "order" (bad name for DB columns) is superior to "num." It is self-regulating if set-up right, like an array, if you remove an element (page or chapter) from the middle, all the others adjust.

[reply]
[d/l]
[select]

      Thanks for the advice and the examples you gave. They were very helpful, and perhaps I may make a few changes in the database design before the site goes into production. The relationship I have is indeed many-to-many but could probably be expressed better like you suggested. However, I did manage to solve my problem. Your post caused me to look up a few things, and I learned what I was doing wrong.

      In a many-to-many relationship, if you're trying to get a single value, you need to use first->, like so:

      $page->chapter->first->num

      [download]

      That does what I needed. Apparently Template Toolkit doesn't require that, as I haven't had to use first with it to do the same thing.

      Again. Thanks for your help. :)

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom