Thanks, it worked.<br><br>But how to do it If I have sub categories of sub category?<br><br>Example is below.<br><br>id name parent _id<br>1 cat1 0<br>2 cat2 1<br>3 cat3 0<br>
4 cat4 2<br>5 cat5 4<br><br>Here, I have cat1 > cat2 -> cat4 > cat5, there may be sub categories of any of sub category.<br><br>Thanks<br><br><div class="gmail_quote">On Fri, Dec 10, 2010 at 9:12 AM, Emmanuel Otton <span dir="ltr"><<a href="mailto:otton@mines-albi.fr">otton@mines-albi.fr</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">
Le 09/12/2010 09:37, linuxsupport a écrit :
<div><div></div><div class="h5"><blockquote 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>__PACKAGE__->has_many("usercats",
"Example::Schema::Result::UserCat",{ "foreign.user_id" => "<a 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 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></div></div>
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;<div class="im"><br>
<br>
<blockquote type="cite">
<div>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></div>
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 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 <a href="http://cat.id" target="_blank">cat.id</a> cat_id, user.*<br>
from cat join usercat on usercat.cat_id = <a href="http://cat.id" target="_blank">cat.id</a><br>
join user on <a href="http://user.id" target="_blank">user.id</a> = usercat.user_id<br>
union select <a href="http://parent.cat.id" target="_blank">parent.cat.id</a> 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 = <a href="http://child_cat.id" target="_blank">child_cat.id</a><br>
join user on <a href="http://user.id" target="_blank">user.id</a> = 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><font color="#888888">
<pre cols="72">--
Emmanuel Otton - Ecole des mines d'Albi-Carmaux - département informatique - Tél: 05 63 49 30 86</pre>
</font></div>
<br>_______________________________________________<br>
List: <a href="mailto:Catalyst@lists.scsys.co.uk">Catalyst@lists.scsys.co.uk</a><br>
Listinfo: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst</a><br>
Searchable archive: <a href="http://www.mail-archive.com/catalyst@lists.scsys.co.uk/" target="_blank">http://www.mail-archive.com/catalyst@lists.scsys.co.uk/</a><br>
Dev site: <a href="http://dev.catalyst.perl.org/" target="_blank">http://dev.catalyst.perl.org/</a><br>
<br></blockquote></div><br>