Join attempt throwing exceptions

Question

I'm sure I'm overlooking something glaringly obvious and I apologize for the newbie question, but I've spent several hours back and forth through documentation for DBIx::Class and Catalyst and am not finding the answer I need...

What I'm trying to do is automate creation of sub-menus based on the contents of my database. I have three tables in the database to do so: maps (in which sub-menu items are found), menus (contains names of top-level menus), maps_menus (assigns maps to top-level menus). I've written a subroutine to return a hash of resultsets, with the plan of using a Template Toolkit nested loop to build the top-level and sub-menus.

Basically, for each top-level menu in menus, I'm trying to run the following query and (eventually) build a sub-menu based on the result:

select * FROM maps JOIN maps_menus ON maps.id_maps = maps_menus.id_maps WHERE maps_menus.id_menus = (current id_menus);

Here is the subroutine, located in lib/MyApp/Schema/ResultSet/Menus.pm

# Build a hash of hashes for menu generation
sub build_menu {
    my ($self, $maps, $maps_menus) = @_;

    my %menus;

    while (my $row = $self->next) {
        my $id = $row->get_column('id_menus');
        my $name = $row->get_column('name');
        my $sub = $maps_menus->search(
            { 'id_maps' => $id },
            { join => 'maps',
                '+select' => ['maps.id_maps'],
                '+as' => ['id_maps'],
                '+select' => ['maps.name'],
                '+as' => ['name'],
                '+select' => ['maps.map_file'],
                '+as' => ['map_file']
            }
        );   

        $menus{$name} = $sub;   

        # See if it worked...
        print STDERR "$name\n";
        while (my $m = $sub->next) {
            my $m_id = $m->get_column('id_maps');
            my $m_name = $m->get_column('name');
            my $m_file = $m->get_column('map_file');

            print STDERR "\t$m_id, $m_name, $m_file\n";
        }
    }
    return \%menus;
}

I am calling this from lib/MyApp/Controller/Maps.pm thusly...

$c->stash(menus => [$c->model('DB::Menus')->build_menu($c->model('DB::Map'), $c->model('DB::MapsMenus'))]);

When I attempt to pull up the page, I get all sorts of exceptions, the top-most of which is:

[error] No such relationship maps on MapsMenus at /home/catalyst/perl5/lib/perl5/DBIx/Class/Schema.pm line 1078

Which, as far as I can tell, originates from the call to $sub->next. I take this as meaning I'm doing my query incorrectly and not getting the results I think I should be. However, I'm not sure what I'm missing.

I found the following lines, defining the relationship to maps, in lib/MyApp/Schema/Result/MapsMenus.pm

__PACKAGE__->belongs_to(
  "id_map",
  "MyApp::Schema::Result::Map",
  { id_maps => "id_maps" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);

...and in lib/MyApp/Schema/Result/Map.pm

__PACKAGE__->has_many(
  "maps_menuses",
  "MyApp::Schema::Result::MapsMenus",
  { "foreign.id_maps" => "self.id_maps" },
  { cascade_copy => 0, cascade_delete => 0 },
);

No idea why it's calling it "maps_menuses" -- that was generated by Catalyst. Could that be the problem?

Any help would be greatly appreciated!

Answer 1

I'd suggest using prefetch of the two relationships which form the many-to-many relationship helper and maybe using HashRefInflator if you don't need access to the row objects.

Note that Catalyst doesn't generate a DBIC (which is btw the official abbreviation for DBIx::Class, DBIx is a whole namespace) schema, SQL::Translator or DBIx::Class::Schema::Loader do. Looks at the docs of the module you've used to find out how to influence its naming. Also feel free to change the names if they don't fit you.