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

[linuxsupport]

Thanks, it worked.

But how to do it If I have sub categories of sub category?

Example is below.

id          name       parent _id
1           cat1        0
2           cat2        1
3           cat3         0
4           cat4         2
5           cat5         4

Here, I have cat1 > cat2 -> cat4 > cat5, there may be sub categories of any
of sub category.

Thanks

On Fri, Dec 10, 2010 at 9:12 AM, Emmanuel Otton <otton at mines-albi.fr> wrote:

>  Le 09/12/2010 09:37, linuxsupport a =E9crit :
>
> I have 3 tables, users, user_cat, and cat, table structure and relationsh=
ip
> 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_nullabl=
e =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.
>
> Are you, really ?
> The navigation path you indicate in your "allcat" many_to_many relationsh=
ip
> does not seem to lead from Cat to Users but from Cat to Cat itself, which
> seems of no practical use.
>
> I rather would have written:
> __PACKAGE__->many_to_many("users","usercat","user");
>
> So that I can get the users in any category this way:
> my $cat =3D $schema->resultset("Cat)->find($my_cat_id);
> my @users_belonging_directly_to_that_category =3D $cat->users;
>
>
>  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?
>
> Now to get recursively all users belonging to this category AND all
> sub-categories is another problem.
>
> The procedural/Perlish/DBIx::Classish solution would be:
> # first, add to Cat.pm an accessor to the sub-categories
> __PACKAGE__->has_many("subcats",""Example::Schema::Result::Cat",{
> "foreign.parent_id" =3D> "self.id" });
>
> # then, in your program:
> my $cat =3D $schema->resultset("Cat)->find($my_cat_id);
> my @users_belonging_to_that_category_and_its_subcats =3D $cat->users;
>
> my @subcats =3D $cat->subcats;
> for my $subcat (@subcats)
>     push @users_belonging_to_that_category_and_its_subcats , $cat->users;
> }
> # assuming the reflexive relationship is one level only
> # i.e. no sub-sub-..etc..-categories
>
>
> A (better in my -humble- opinion) solution would be more SQL-oriented
> (after all, isn't the declarative SQL way the reason why we all use RDBMS=
es
> ?)
>
> First of all, create a view giving what you want:
>
> create view users_by_cat_and_subcats as
> select cat.id cat_id, user.*
>   from cat join usercat on usercat.cat_id =3D cat.id
>   join user on user.id =3D usercat.user_id
> union select parent.cat.id cat_id , user.*
>   from cat parent_cat join cat child_cat on child_cat.parent_id =3D
> parent_cat.cat_id
>   join usercat on usercat.cat_id =3D child_cat.id
>   join user on user.id =3D usercat.user_id ;
>
> Then create (automatically, by make_schema_at) a UsersByCatAndSubcat.pm to
> map this table to a DBIx::Class and just use it:
> my @users_belonging_to_that_category_and_its_subcats =3D
>    $schema->resultset('UsersByCatAndSubcat')->search( { cat_id =3D>
> $my_category } )->all;
>
> It's always difficult to use the right tool, and I've seen ugly things li=
ke
> simple left joins replaced by 15 lines of while, if defined, push,etc...
> RDBMSes have dominated our domain for nearly forty years for a reason,
> which is the expressive power of SQL, itself based on strong theoretical
> mathematical background.
> My advice is: don't forget you have a RDBMS, and thus the full power of
> SQL, at your disposal.
>
> Sorry for this answer to a slightly off-catalyst-topic question; I will
> subscribe to the DBIx::Class list right away.
>
> --
> Emmanuel Otton - Ecole des mines d'Albi-Carmaux - d=E9partement informati=
que - T=E9l: 05 63 49 30 86
>
>
> _______________________________________________
> 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/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101216/8497c=
79e/attachment.htm