DBIx:Class many-to-many relationship with additional attribute

I'm working with DBIx::Class in Catalyst framework. My local goal is to add a new many-to-many relationship between users and, let's say, tasks. But there's one little trick I need. User can have different roles in task (like 'worker' or 'spectator').

So I have users table with these fields:

• id
• name

I have task table with these fields:

• id
• title
• description

And I have relationship table user_task开发者_StackOverflows with these fields:

• user_id
• task_id
• role

I have set up has_many from users to user_tasks, has_many from tasks to user_tasks and corresponding many_to_many relationships between users and tasks. And that plain part works as it should.

Then, for example, I want to get my user list including user's role in task identified by $task_id:

my $users = $schema->resultset('User')->with_task_role($task_id);
while (my $u = $users->next) {
    print "User: " . $u->name . ", role: " . $u->get_column('task_role');
}

So how should I code this with_task_role custom resultset to get this additional field with user's task role in my query?

First of all many-to-many is not a relationship. It's a accessor (a relationship bridge).

Second, the DBIx::Class has an excellent documentation. Take a look at join/prefetch. In your ResultSet/User.pm file you should have something like:

sub with_task_role {
    my ($self, $task_id) = @_;

    return $self->search({
            'task.task_id' => $task_id,
        },
        {
            join     => { 'user_task' => 'task' },
            prefetch => { 'user_task' => 'task' },
        },
    );
}

PS: Sorry, I didn't see that Ashley already answered PS2: Before the last line "})" should be ")" only (fixed it)

This is some User resultset code, unchanged, from the XUL (which is no longer supported by any browsers) slideshow of the DBIC master class (.xul resource). I highly recommend downloading the slides, which read fine as plain text, to review. They were a great eye-opener for resultsets for me.

You'll have to adjust the result source names to match your own but this should be what you want and a bit more flexible to boot with the _role_to_id which allows you to pass role objects or ids.

  sub with_role {
    my ($self, $role) = @_;
    $self->search({
        'role_links.role_id' => $role->id
      },
      { join => 'role_links' }
    );
  }

  sub _role_to_id {
    my ($self, $role) = @_;
    return blessed($role) ? $role->id : $role;
  }

  sub with_any_role {
    my ($self, @roles) = @_;
    $self->search({
        'role_links.role_id' => {
          -in => [
            map { $self->_role_to_id($_) } @roles
          ]
        }
      },
      { join => 'role_links' }
    );
  }