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 cat_id, user.*
from cat join usercat on usercat.cat_id = cat.id
join user on user.id = usercat.user_id
union select 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
join user on 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.
--
Emmanuel Otton - Ecole des mines d'Albi-Carmaux - département informatique -
Tél: 05 63 49 30 86
_______________________________________________
List: Catalyst@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/
