many-to-many-to-many accessor using DBIx::Class

Question

Using DBIx::Class, I am trying to create a many-to-many accessor (or even just a has-many relationship) that spans two link tables.

The three data tables in question are Users, Roles and Pages, with the two link tables UserRoles and RolePages. These tables are related thus:

• a User has many UserRoles
• a Role has many UserRoles
• a Role has many RolePages
• a Page has many RolePages

The Catalyst helper script created these relationships and accessors for me:

package MyApp::Schema::Result::User;

__PACKAGE__->has_many(
  "user_roles",
  "MyApp::Schema::Result::UserRole",
  { "foreign.username" => "self.username" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->many_to_many("roles", "user_roles", "role");


package MyApp::Schema::Result::Role;

__PACKAGE__->has_many(
  "role_pages",
  "MyApp::Schema::Result::RolePage",
  { "foreign.role" => "self.role" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->has_many(
  "user_roles",
  "MyApp::Schema::Result::UserRole",
  { "foreign.role" => "self.role" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->many_to_many("page_names", "role_pages", "page_name")

__PACKAGE__->many_to_many("usernames", "user_roles", "username");


package MyApp::Schema::Result::Page;

__PACKAGE__->has_many(
  "role_pages",
  "MyApp::Schema::Result::RolePage",
  { "foreign.page_name" => "self.page_name" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->many_to_many("roles", "role_pages", "role");


package MyApp::Schema::Result::UserRole;

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

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


package MyApp::Schema::Result::RolePage;

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

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

My end goal is to have a clean way to get a list of the pages that should be displayed for the current user. I.e.:

foreach my $page ($c->user->pages) {
    # do something with $page
}

I did have this method in the MyApp::Schema::Result::User class:

sub pages {
    my ($self) = @_;

    return $self->result_source->schema->resultset('RolePage')->search(
        {
            'username.username' => $self->username,
        },
        {
            join => [
                {
                    role => {user_roles => 'username'},
                },
                'page_name',
            ],
        }
    );
}

which worked until I added a class to extend MyApp::Schema::Result::RolePage. That class starts off like this:

package MyApp::Schema::ResultSet::RolePage;

use Moose;
use namespace::autoclean;
extends qw/MyApp::Schema::ResultSetX::DisplayTable/;

has '+name' => ( default => 'RolePage' );

I've used this technique throughout my app, to create various result sets, and have only run into trouble with it now. I get this error:

Caught exception in Lifeway::Controller::Root->auto "Single parameters to new() must be a HASH ref

which is caused by my MyApp::Schema::Result::User->pages() method. So, to avoid this error, I thought I'd try to create a many-to-many accessor (or even just a has-many relationship) from Users to Pages.

Is it possible to create such an accessor/relationship that spans two link tables? If so, how? I can't find an example in the docs, and have run out of ideas myself. Failing that, any ideas on why I'm getting that error with my pages() method?

Answer 1

As far as I know, there is no built-in support for many-to-many-to-many relationships, but it's not too hard to roll your own.

In Schema::Result::User.pm

sub pages {
  my $self = shift;
  return $self->search_related('user_roles')
    ->search_related('role')
    ->search_related('role_pages')
    ->search_related('page_name');
    # Alternatively, if you need to eliminate duplicates:
    # ->search_related('page_name', {}, {distinct => 1});
}

This is pretty close to a functional equivalent to a standard DBIx many-to-many relationship bridge, I think.