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:

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"
+ },
);

[download]

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
}

[download]

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

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

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

[download]

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' );

[download]

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 is 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?

Update

Here is a way to roll-your-own many-to-many-to-many accessor (thanks RickF):

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});
}

[download]

In reply to many-to-many-to-many accessor using DBIx::Class by waz

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.