[Catalyst] DBIx::Class many_to_many relationship

[Emmanuel OTTON]

Le 30 nov. 2010 à 17:02, linuxsupport a écrit :

> Hi,
> I am new to Catalyst and DBIx::Class, trying to use many_to_many
> relationship.
> I have 3 tables, users, user_groups, and group, table structure and
> relationship are setup as follows.
> 
> User.pm
> __PACKAGE__->add_columns(
>  "id",
>  { data_type => "integer", is_nullable => 0 },
>  "username",
>  { data_type => "text", is_nullable => 1 },
>  "password",
>  { data_type => "text", is_nullable => 1 },
>  "email_address",
>  { data_type => "text", is_nullable => 1 },
>  "first_name",
>  { data_type => "text", is_nullable => 1 },
>  "last_name",
>  { data_type => "text", is_nullable => 1 },
>  "active",
>  { data_type => "integer", is_nullable => 1 },
> );
> __PACKAGE__->set_primary_key("id");
> 
> __PACKAGE__->has_many("usergroups", "Example::Schema::Result::UserGroup",{
> "foreign.user_id" => "self.id" },);
> __PACKAGE__->many_to_many(group => 'usergroups', 'group');
> 
> UserGroup.pm
> 
> __PACKAGE__->add_columns(
>  "user_id",
>  { data_type => "integer", is_nullable => 0 },
>  "group_id",
>  { data_type => "integer", default_value => 0, is_nullable => 0 },
> );
> __PACKAGE__->set_primary_key("user_id", "group_id");
> 
> __PACKAGE__->belongs_to("user", "Example::Schema::Result::User", { id =>
> "user_id" },{ join_type => "LEFT" },);
> __PACKAGE__->belongs_to("group", "Example::Schema::Result::Group", { id =>
> "group_id" },{ join_type => "LEFT" },);
> 
> Group.pm
> 
> __PACKAGE__->add_columns(
>  "id",
>  { data_type => "integer", is_nullable => 0 },
>  "group",
>  { data_type => "text", is_nullable => 0 },
> );
> __PACKAGE__->set_primary_key("id");
> 
> __PACKAGE__->has_many("usergroup","Example::Schema::Result::UserGroup",{
> "foreign.group_id" => "self.id" },);
> 
> Can anyone tell me how I can retrieve all the users who are member of a
> group called 'manager'?

1 - DECLARATION
---------------
First, declare your N:M link from group to user, using the method many_to_many, i.e. in your Group.pm, manually add this (at the end of the file, AFTER the line that says "DO NOT MODIFY THIS OR ANYTHING ABOVE" if you generated your classes using the DBIx::Class::Schema::Loader method "make_schema_at", which I highly recommend over the tedious manual method ):

__PACKAGE__->many_to_many('users','usergroup','user');

This many_to_many method adds to any Group object an accessor called "users", giving directly access to the linked users.
The 3 arguments are a name and two accessors constituting the path to be used:
 - the accessor name, chosed by you (I tend to recommand the plural, which by the way you did not use for your "has_many" accessors)
 - the has_many accessor leading from Group to UserGroup,
 - the belongs_to accessor leading from UserGroup to User.

2 - USE
-------
Then, when you need your users (supposing there is only one group called 'manager'), you just have to get your Group object, and apply the just created accessor on it:

my $manager_group = $schema->resultset('Group')->search( { group => 'manager' } )->next;
my @users_belonging_to_manager_group = $manager_group->users;

And voila.

By the way, the many_to_many method has also created a "add_to_users" method:
Suppose Joe was just promoted as manager:
my $joe = $schema->resultset('User')->find('Joe');
my $manager_group = $schema->resultset('Group')->search( { group => 'manager' } )->next;
$manager_group->add_to_users($joe);

The last line does the right thing(s) to link joe to the manager group.

I've been using it since a long time, it works like a charm.
--
Emmanuel OTTON - Responsable informatique - Ecole des mines d'Albi-Carmaux - Tél: 05 63 49 30 86