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

[linuxsupport]

Thanks for the response, I am really sorry, I thought my first message did
not reach to the list.

cat table had 3 fields id, name and parent_id, parent_id refer to id when it
is a sub category
I have a relationship to cat_id with user_cat table.

How can I create relation with parent_id here?

Basically, I need all the users which belongs to a cat or its all sub cat.

Thanks

On Fri, Dec 10, 2010 at 1:18 AM, Wolfgang Kinkeldei
<wolfgang at kinkeldei.de>wrote:

> 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 structu=
re.
>
> 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 =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 "allca=
t"
> 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??,"".(($/=3Dq|Cms)+-03467:;<=3D|)=3D~tr!C-z -B! -z=
!)x
> '( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
> ' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->
>
>
>
>
> _______________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101210/4a8=
4aa4d/attachment.htm