3

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?

Waz
  • 653
  • 6
  • 12
  • The search method will return a single object rather than a ResultSet when there is only one item in the RS (or undef if no match is found) - does the problem go away if you change it to ->search_rs(...), forcing it to always return a ResultSet? – RickF Jul 05 '12 at 21:52
  • No, unfortunately I still get the `Single parameters to new() must be a HASH ref` error. It's probably better to keep it as `search_rs`, though, to ensure it returns a predictable data type. Thanks. – Waz Jul 05 '12 at 22:05
  • That error looks like a Moose error, not a DBIx error. Somewhere you are calling new() with a single non-hashref argument - are you sure the problem is in DBIx? Can you create a $user instance and call $user->pages() directly to see if it is returning what you think it is? – RickF Jul 06 '12 at 13:49
  • You're right. The exception is raised in `/opt/ActivePerl-5.14/site/lib/Moose/Object.pm`. – Waz Jul 06 '12 at 21:44
  • I put `die Dumper $c->user->pages;` at the start of my root controller. It seemed to return something sensible for both your new `pages()` method (as mentioned below) and my old one. However, my old method causes the error when used in this statement: `$c->user->pages->search(undef, {columns => ['page_name'], distinct => 1, order_by => 'sort_order'})->get_column('page_name')->all` – Waz Jul 07 '12 at 01:53
  • I'm not sure if you can pass undef as the first argument to search - it might need to be an empty hashref instead. – RickF Jul 09 '12 at 00:15
  • `->pages->search(undef,...)` seems to work ok, once I have a good `pages()` method. – Waz Jul 12 '12 at 06:38

1 Answers1

5

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.

RickF
  • 1,812
  • 13
  • 13