<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Le 09/12/2010 09:37, linuxsupport a écrit :
<blockquote
cite="mid:AANLkTi=opDSwh+ueL71HLN2-9z8umbdSZOHBn=CrLKhk@mail.gmail.com"
type="cite">I have 3 tables, users, user_cat, and cat, table
structure and relationship are setup as follows.<br>
User.pm<br>
__PACKAGE__->add_columns(<br>
"id", { data_type => "integer", is_nullable => 0 },<br>
"username",{ data_type => "text", is_nullable => 1 },<br>
"password", { data_type => "text", is_nullable => 1 },<br>
"email_address", { data_type => "text", is_nullable => 1
},<br>
"first_name", { data_type => "text", is_nullable => 1 },<br>
"last_name", { data_type => "text", is_nullable => 1 },<br>
"active", { data_type => "integer", is_nullable => 1 },<br>
);<br>
__PACKAGE__->set_primary_key("id");<br>
<div id=":u0">__PACKAGE__->has_many("usercats",
"Example::Schema::Result::UserCat",{ "foreign.user_id" => "<a
moz-do-not-send="true" href="http://self.id/" target="_blank">self.id</a>"
},);<br>
__PACKAGE__->many_to_many(cats => 'usercats', 'cat');<br>
<br>
UserCat.pm<br>
__PACKAGE__->add_columns(<br>
"user_id", { data_type => "integer", is_nullable => 0
},<br>
"cat_id", { data_type => "integer", default_value => 0,
is_nullable => 0 },<br>
);<br>
__PACKAGE__->set_primary_key("user_id", "cat_id");<br>
__PACKAGE__->belongs_to("user",
"Example::Schema::Result::User", { id => "user_id" },{
join_type => "LEFT" },);<br>
__PACKAGE__->belongs_to("cat",
"Example::Schema::Result::Cat", { id => "cat_id" },{
join_type => "LEFT" },);<br>
<br>
Cat.pm<br>
__PACKAGE__->add_columns(<br>
"id", { data_type => "integer", is_nullable => 0 },<br>
"cat_name", { data_type => "text", is_nullable => 0 },<br>
"parent_id", { data_type => "integer", is_nullable => 0
},<br>
);<br>
__PACKAGE__->set_primary_key("id","parent_id");<br>
__PACKAGE__->has_many("usercat","Example::Schema::Result::UserCat",{
"foreign.cat_id" => "<a moz-do-not-send="true"
href="http://self.id/" target="_blank">self.id</a>" },);<br>
__PACKAGE__>many_to_many("allcat", "usercat', "cat");<br>
<br>
I am able to retrieve all users in any particular category using
"allcat" many_to_many relationship.<br>
</div>
</blockquote>
Are you, really ?<br>
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.<br>
<br>
I rather would have written:<br>
__PACKAGE__->many_to_many("users","usercat","user");<br>
<br>
So that I can get the users in any category this way:<br>
my $cat = $schema->resultset("Cat)->find($my_cat_id);<br>
my @users_belonging_directly_to_that_category = $cat->users;<br>
<br>
<blockquote
cite="mid:AANLkTi=opDSwh+ueL71HLN2-9z8umbdSZOHBn=CrLKhk@mail.gmail.com"
type="cite">
<div id=":u0">In cat table I have both category and sub category,
if a row have parent_id > 0 then it is sub category.<br>
How can I get all users belong to one category and its sub
categories? </div>
</blockquote>
Now to get recursively all users belonging to this category AND all
sub-categories is another problem.<br>
<br>
The procedural/Perlish/DBIx::Classish solution would be:<br>
# first, add to Cat.pm an accessor to the sub-categories<br>
__PACKAGE__->has_many("subcats",""Example::Schema::Result::Cat",{
"foreign.parent_id" => "<a moz-do-not-send="true"
href="http://self.id/" target="_blank">self.id</a>" });<br>
<br>
# then, in your program:<br>
my $cat = $schema->resultset("Cat)->find($my_cat_id);<br>
my @users_belonging_to_that_category_and_its_subcats =
$cat->users;<br>
<br>
my @subcats = $cat->subcats;<br>
for my $subcat (@subcats)<br>
push @users_belonging_to_that_category_and_its_subcats ,
$cat->users;<br>
}<br>
# assuming the reflexive relationship is one level only<br>
# i.e. no sub-sub-..etc..-categories<br>
<br>
<br>
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 ?)<br>
<br>
First of all, create a view giving what you want:<br>
<br>
create view users_by_cat_and_subcats as<br>
select cat.id cat_id, user.*<br>
from cat join usercat on usercat.cat_id = cat.id<br>
join user on user.id = usercat.user_id<br>
union select parent.cat.id cat_id , user.*<br>
from cat parent_cat join cat child_cat on child_cat.parent_id =
parent_cat.cat_id<br>
join usercat on usercat.cat_id = child_cat.id<br>
join user on user.id = usercat.user_id ;<br>
<br>
Then create (automatically, by make_schema_at) a
UsersByCatAndSubcat.pm to map this table to a DBIx::Class and just
use it:<br>
my @users_belonging_to_that_category_and_its_subcats = <br>
$schema->resultset('UsersByCatAndSubcat')->search( { cat_id
=> $my_category } )->all;<br>
<br>
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...<br>
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.<br>
My advice is: don't forget you have a RDBMS, and thus the full power
of SQL, at your disposal.<br>
<br>
Sorry for this answer to a slightly off-catalyst-topic question; I
will subscribe to the DBIx::Class list right away.<br>
<pre class="moz-signature" cols="72">--
Emmanuel Otton - Ecole des mines d'Albi-Carmaux - département informatique - Tél: 05 63 49 30 86</pre>
</body>
</html>