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

[Hauck, William B.]

Not sure how to do it in a DBIC-like fashion, but these few links show you how to do it in SQL for PostgreSQL and MySQL.  Oracle and DB2 have hierarchical functionality built-in.

http://www.postgresql.org/docs/8.4/static/queries-with.html

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

http://onlamp.com/lpt/a/5007


On 12/16/10 4:40 AM, "linuxsupport" <lin.support at gmail.com> wrote:

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 écrit :
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 <http://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 <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 = $schema->resultset("Cat)->find($my_cat_id);
 my @users_belonging_directly_to_that_category = $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" => "self.id <http://self.id/> " });

 # then, in your program:
 my $cat = $schema->resultset("Cat)->find($my_cat_id);
 my @users_belonging_to_that_category_and_its_subcats = $cat->users;

 my @subcats = $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 <http://cat.id>  cat_id, user.*
   from cat join usercat on usercat.cat_id = cat.id <http://cat.id>
   join user on user.id <http://user.id>  = usercat.user_id
 union select parent.cat.id <http://parent.cat.id>  cat_id , user.*
   from cat parent_cat join cat child_cat on child_cat.parent_id = parent_cat.cat_id
   join usercat on usercat.cat_id = child_cat.id <http://child_cat.id>
   join user on user.id <http://user.id>  = 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 =
    $schema->resultset('UsersByCatAndSubcat')->search( { cat_id => $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.


CONFIDENTIALITY NOTICE: This E-Mail is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you have received this communication in error, please do not distribute and delete the original message.  Please notify the sender by E-Mail at the address shown. Thank you for your compliance