DBIx: many-to-many relationship with optional attribute

Question

I am working with a DBIx :: Class database in Catalyst. My local goal is to add new many-to-many relationships between users and, say, tasks. But I need one little trick. The user may have different roles in the task (for example, “worker” or “viewer”).

So, I have a user table with these fields:

• ID
• name

I have a task table with these fields:

• ID
• title
• Description

And I have a user_tasks relationship table with these fields:

• user_id
• TASK_ID
• role

I created has_many from users to user_tasks , has_many from tasks to user_tasks and the corresponding many_to_many relationships between users and tasks . And this simple part works as it should.

Then, for example, I want to get a list of users, including the user role, in the task given 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 encode this c_task_role custom result set to get this extra field with the user task role in my query?

Answer 1

First of all, many-to-many are not relationships. This is an accessor (bridge of communications).

Secondly, DBIx :: Class has excellent documentation. Take a look at compound / prefetch. In the file ResultSet / User.pm 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 did not see that Ashley already answered PS2: before the last line "})" should be ")" only (fixed)

Answer 2

This is some User result set code, unchanged, from XUL (which is no longer supported by any browsers) DBIC master class slideshow (.xul resource). I highly recommend downloading slides that read well in plain text for viewing. They were a great discovery for me.

You will need to edit the source names of the results to fit your own, but that should be what you want and a little more flexible for loading with _role_to_id , which allows you to pass role objects or identifiers.

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