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