[Dbix-class] Search_related with many_to_many relationship

[Christian Lackas]

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 did get it to work as a method of the Model itself:

    package MyApp::Model::MyAppDB;
    [...]
    sub userprojectlist {
        my ($self, $userid, $mode) = @_;        # mode: crudm
        my $proj = $self->resultset('Projects')->search(
            { 'map_role_proj.perm' => $mode, 'user.id' => $userid },
            { 
                join => { map_role_proj => { role => { map_user_role => 'user' } } },
                distinct => 1,
                order_by => 'projectpath',
            }
        );
    }

However, here I start from a ResultSet, while above I have a Row, right?

In the end, I would like to use above projects() method in Catalyst in a
TT template:

    [% FOR p IN c.user.projects %]
        Project: [% p.projectname %]
    [% END %]

Thanks for your help.
Happy to post more code, did not want to blow this mail up, though. Just
let me know if you need anything else.

Christian

-- 
http://www.spect-ct.com/    NanoSPECT/CT multi-modality imaging
http://www.invicro.com/     In Imaging Yours