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!

I've run the query I quoted in the first block directly in sqlite and I get the results I would expect. The abstraction into DBIx::Class language confuses me, though, and I very well could be doing it wrong there. :-/ — user3396385
Sounds like it your relationship isn't set up right (as the error message says). I'd look over DBIx documentation again. — Zach Leighton
I think you're right, Zach, but I'm still not sure how to fix it. Making some edits to the main post to reflect what I know... — user3396385
Okay, found the problem. I had the direction of the join reversed in the relationship. Putting the "has_many" in maps_menus and the "belongs_to" in maps and doing the join on maps instead of maps_menus got it working. So simple! — user3396385

Alexander Hartmaier Alexander Hartmaier · Accepted Answer · 2015-05-06T18:30:45

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.

Join attempt throwing exceptions

1 Answers