[Dbix-class] Retrieve all users belong to a category and all its sub categories

[Wolfgang Kinkeldei]

Hi,

didn't you already post this message?

looks like you have a relation missing in your "Cat" table. In your code snippet below I cannot see a relation containing parent_id. I guess it references another record in the same table, thus building a tree structure.

Maybe, you should take a look at one of these packages:
  * DBIx::Class::Tree
  * DBIx::Class::Tree::AdjacencyList
  * DBIx::Class::Tree::NestedSet

Am 09.12.2010 um 19:56 schrieb linuxsupport:

> Hi,
> 
> I am using DBIX::Class with Catalyst
> 
> 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?
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk


Best,

Wolfgang Kinkeldei

-- 

' /\_/\ ' .print[split??,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->