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

[Emmanuel Otton]

Le 09/12/2010 09:37, linuxsupport a =E9crit :
> 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 <http://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 <http://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 =

relationship 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 <http://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 =

RDBMSes ?)

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 =

like 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 informatiqu=
e - T=E9l: 05 63 49 30 86

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20101210/4b804=
06f/attachment.htm