[Catalyst] Retrieve all users belong to a category and all its sub categories

[David Schmidt]

On Thu, Dec 9, 2010 at 9:37 AM, linuxsupport <lin.support at gmail.com> wrote:
> I have 3 tables, users, user_cat, and cat, 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("usercats", "Example::Schema::Result::UserCat",{
> "foreign.user_id" => "self.id" },);
> __PACKAGE__->many_to_many(cats => 'usercats', 'cat');
>
> UserCat.pm
>
> __PACKAGE__->add_columns(
>   "user_id",
>   { data_type => "integer", is_nullable => 0 },
>   "cat_id",
>   { data_type => "integer", default_value => 0, is_nullable => 0 },
> );
> __PACKAGE__->set_primary_key("user_id", "cat_id");
>
> __PACKAGE__->belongs_to("user", "Example::Schema::Result::User", { id =>
> "user_id" },{ join_type => "LEFT" },);
> __PACKAGE__->belongs_to("cat", "Example::Schema::Result::Cat", { id =>
> "cat_id" },{ join_type => "LEFT" },);
>
> Cat.pm
>
> __PACKAGE__->add_columns(
>   "id",
>   { data_type => "integer", is_nullable => 0 },
>   "cat_name",
>   { data_type => "text", is_nullable => 0 },
>  "parent_id",
>   { data_type => "integer", is_nullable => 0 },
> );
> __PACKAGE__->set_primary_key("id","parent_id");
>
> __PACKAGE__->has_many("usercat","Example::Schema::Result::UserCat",{
> "foreign.cat_id" => "self.id" },);
> __PACKAGE__>many_to_many("allcat", "usercat', "cat");
>
> I am able to retrieve all users in any particular category using "allcat"
> many_to_many relationship.
>
> In cat table I have both category and sub category, if a row have parent_id
>> 0 then it is sub category.
>
> How can I get all users belong to one category and its sub categories?
>
> Thanks in advance.
>
>
> _______________________________________________
> List: Catalyst at 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/
>
>

That's entirely a DBIx::Class question. You might want to ask in
#dbix-class on irc.perl.org or at the DBIx::Class mailinglist
http://lists.scsys.co.uk/mailman/listinfo/dbix-class

david