[Dbix-class] Search_related with many_to_many relationship

[Jason Gottshall]

Christian Lackas wrote:
> Hi Everybody,
> 
> I have a problem with using search_related in more complex cases.
> DBIx::Class is used in Catalyst, where I would like to find all projects
> a user has access to.
> The tables look like:
>     user
>         has_many        map_user_role
>         many_to_many    roles
>     roles
>         has_many        map_user_role
>         many_to_many    users
>         has_many        map_role_proj
>         many_to_many    projects
>     projects
>         has_many        map_role_proj
>         many_to_many    roles
> 
> With these two join tables linking users to roles and finally roles to
> projects:
> 
>     map_user_role
>         belongs_to      user
>         belongs_to      role
>     map_role_proj
>         belongs_to      role
>         belongs_to      project
> 
> Now, I have a user and would like to find the roles the user is part of
> (via map_user_role) and the projects which belong to these roles (via
> map_role_proj).
> 
> For this I defined a method in the Users Schema file such as:
> 
>     package MyDB::Schema::Users;
>     [...]
>     sub projects {
>         my ($self, $mode) = @_;
>         my $proj = $self->search_related('map_user_role', # XXX
>                 { 'map_role_proj.perm' => $mode },
>                 {
>                     join => { map_user_role => { role => { map_role_proj => 'projects' } } },
>                     distinct => 1,
>                     order_by => 'projectpath',
>                 }
>         );
>     }
>  
> But I don't understand how to use search_related here. I already found
> that I cannot use the many_to_many relationship, so I assume 'XXX' above
> has to be 'map_user_role', but how to continue from there?
> 

I think you can probable use at least one of your many_to_many 
shortcuts; it's just sugar for two sequential "search_related" calls 
anyway. Try something like this (untested, of course):

     package MyDB::Schema::Users;
     sub projects {
         my ( $self, $mode ) = @_;
         return $self->roles->search_related( 'map_role_proj',
             { 'map_role_proj.perm' => $mode },
             )->search_related(
             'projects',
             {},
             {   distinct => 1,
                 order_by => 'projectpath',
             }
             );
     }

HTH,
Jason

-- 
Jason Gottshall
jgottshall at capwiz.com