FAQ
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'?

Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101130/7f1a05f6/attachment.htm

Search Discussions

  • Steve at Nov 30, 2010 at 4:15 pm
    I recommend you create a 'Example::Schema::ResultSet::Group' resultset
    class. Presumably
    you already have a 'Result' directory where your table classes reside,
    so create a ResultSet
    directory (if you don't already have one).

    Look here:
    http://search.cpan.org/~frew/DBIx-Class-0.08124/lib/DBIx/Class/ResultSet.pm
    <http://search.cpan.org/%7Efrew/DBIx-Class-0.08124/lib/DBIx/Class/ResultSet.pm>for
    direction,
    but essentially this is where you would set up queries pertaining to
    your 'Groups'.

    You'll do a search of all the related Users having a group name =
    'manager' or something
    similar.

    Steve
    On 11/30/2010 11:02 AM, linuxsupport wrote:
    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
    <http://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 <http://self.id>" },);

    Can anyone tell me how I can retrieve all the users who are member of
    a group called 'manager'?

    Thanks


    _______________________________________________
    List: Catalyst@lists.scsys.co.uk
    Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
    Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
    Dev site: http://dev.catalyst.perl.org/
  • Emmanuel OTTON at Nov 30, 2010 at 4:55 pm

    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
  • Linuxsupport at Nov 30, 2010 at 5:22 pm
    Thanks Emmanuel, worked as I wanted to, much appreciated.
    On Tue, Nov 30, 2010 at 10:25 PM, Emmanuel OTTON wrote:

    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


    _______________________________________________
    List: Catalyst@lists.scsys.co.uk
    Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
    Searchable archive:
    http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
    Dev site: http://dev.catalyst.perl.org/
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101130/e1fae9da/attachment.htm
  • Kutbuddin Doctor at Nov 30, 2010 at 5:57 pm
    In the classes you have written, you are using the same name for the
    many_to_many method from ("group") in User.pm as you have used in method
    between UserGroup to Group.

    __PACKAGE__->many_to_many(group => 'usergroups', 'group');

    I have seen this cause problems in my hands when using it for User Role
    relationships (Authorization::Role and ACL).. I would recommend avoiding
    reusing the relationship accessor names.

    On 11/30/10 8:02 AM, linuxsupport wrote:
    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
    <http://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 <http://self.id>" },);

    Can anyone tell me how I can retrieve all the users who are member of
    a group called 'manager'?

    Thanks

    --
    Kutbuddin Doctor, PhD
    Bioinformatics Shared Resource,
    Sanford-Burnham Medical Research Institute
    http://www.sanfordburnham.org/

    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101130/41d74296/attachment.htm

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedNov 30, '10 at 4:02p
activeNov 30, '10 at 5:57p
posts5
users4
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2021 Grokbase