I have 3 tables, users, user_cat, and cat, table structure and relationship are setup as follows.<br><br>User.pm<br><br>
__PACKAGE__->add_columns(<br> "id",<br> { data_type => "integer", is_nullable => 0 },<br> "username",<br> { data_type => "text", is_nullable => 1 },<br> "password",<br>
{ data_type => "text", is_nullable => 1 },<br> "email_address",<br> { data_type => "text", is_nullable => 1 },<br> "first_name",<br> { data_type => "text", is_nullable => 1 },<br>
"last_name",<br> { data_type => "text", is_nullable => 1 },<br> "active",<br> { data_type => "integer", is_nullable => 1 },<br>);<br>__PACKAGE__->set_primary_key("<div id=":u0">
id");<br>
<br>__PACKAGE__->has_many("usercats", "Example::Schema::Result::UserCat",{ "foreign.user_id" => "<a href="http://self.id/" target="_blank">self.id</a>" },);<br>__PACKAGE__->many_to_many(cats => 'usercats', 'cat');<br>
<br>UserCat.pm<br><br>__PACKAGE__->add_columns(<br> "user_id",<br> { data_type => "integer", is_nullable => 0 },<br> "cat_id",<br> { data_type => "integer", default_value => 0, is_nullable => 0 },<br>
);<br>__PACKAGE__->set_primary_key("user_id", "cat_id");<br><br>__PACKAGE__->belongs_to("user", "Example::Schema::Result::User", { id => "user_id" },{ join_type => "LEFT" },);<br>
__PACKAGE__->belongs_to("cat", "Example::Schema::Result::Cat", { id => "cat_id" },{ join_type => "LEFT" },);<br><br>Cat.pm<br><br>__PACKAGE__->add_columns(<br> "id",<br>
{ data_type => "integer", is_nullable => 0 },<br> "cat_name",<br> { data_type => "text", is_nullable => 0 },<br> "parent_id",<br> { data_type => "integer", is_nullable => 0 },<br>
);<br>__PACKAGE__->set_primary_key("id","parent_id");<br><br>__PACKAGE__->has_many("usercat","Example::Schema::Result::UserCat",{ "foreign.cat_id" => "<a href="http://self.id/" target="_blank">self.id</a>" },);<br>
__PACKAGE__>many_to_many("allcat", "usercat', "cat");<br>
<br>I am able to retrieve all users in any particular category using "allcat" many_to_many relationship.<br><br>In cat table I have both category and sub category, if a row have parent_id > 0 then it is sub category.<br>
<br>How can I get all users belong to one category and its sub categories? <br><br>Thanks in advance.<br><br></div>