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

[linuxsupport]

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 =3D> "integer", is_nullable =3D> 0 },
  "username",
  { data_type =3D> "text", is_nullable =3D> 1 },
  "password",
  { data_type =3D> "text", is_nullable =3D> 1 },
  "email_address",
  { data_type =3D> "text", is_nullable =3D> 1 },
  "first_name",
  { data_type =3D> "text", is_nullable =3D> 1 },
  "last_name",
  { data_type =3D> "text", is_nullable =3D> 1 },
  "active",
  { data_type =3D> "integer", is_nullable =3D> 1 },
);
__PACKAGE__->set_primary_key("
id");

__PACKAGE__->has_many("usercats", "Example::Schema::Result::UserCat",{
"foreign.user_id" =3D> "self.id" },);
__PACKAGE__->many_to_many(cats =3D> 'usercats', 'cat');

UserCat.pm

__PACKAGE__->add_columns(
  "user_id",
  { data_type =3D> "integer", is_nullable =3D> 0 },
  "cat_id",
  { data_type =3D> "integer", default_value =3D> 0, is_nullable =3D> 0 },
);
__PACKAGE__->set_primary_key("user_id", "cat_id");

__PACKAGE__->belongs_to("user", "Example::Schema::Result::User", { id =3D>
"user_id" },{ join_type =3D> "LEFT" },);
__PACKAGE__->belongs_to("cat", "Example::Schema::Result::Cat", { id =3D>
"cat_id" },{ join_type =3D> "LEFT" },);

Cat.pm

__PACKAGE__->add_columns(
  "id",
  { data_type =3D> "integer", is_nullable =3D> 0 },
  "cat_name",
  { data_type =3D> "text", is_nullable =3D> 0 },
 "parent_id",
  { data_type =3D> "integer", is_nullable =3D> 0 },
);
__PACKAGE__->set_primary_key("id","parent_id");

__PACKAGE__->has_many("usercat","Example::Schema::Result::UserCat",{
"foreign.cat_id" =3D> "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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101209/de7dc=
364/attachment.htm