[Dbix-class] Problem retrieving data

[SHAD0WRUNNER at gmx.de]

-------- Original-Nachricht --------
Datum: Mon, 21 May 2007 13:25:18 +0100 (BST)
Von: Anthony Gardner <cyclewood_ltd at yahoo.co.uk>
An: dbix-class at lists.rawmode.org
Betreff: [Dbix-class] Problem retrieving data

> This is probably bread 'n butter to you but ........
> 
> I have the following table relationships .....
> 
> groups ---< categories ---< subcategories ----< products
> 
> for a group, I want to retrieve all of its categories, the catgeories'
> subcats etc.
> 
> i have this .........
> 
> ## switch between list and rs context
> my $group_rs =
> my @group = $schema->resultset('ProductGroup')->search( { 'me.id' => 18 },
>                                                            { select => [
> 'me.name', 'categories.name', 'subcategories.name', 'products.name'],
>                                                                 as => [
> qw|group_name category_name subcategory_name product_name|],
>                                                                join => {
> categories => { subcategories => 'products' }}
>                                                            });
> 

I recommand use relations between tables:
my $group_rs = $schema->resultset('ProductGroup')
                      ->search( { 'me.id' => 18})

while ($group=$rs->next){
  foreach my $categorie ($group->categories){
     foreach my $sub_categorie ($categorie->subcategories){   
        foreach my $product ($sub_categorie->products){  
           print $group->group_name, ...;
        }
     }
  }
}


If it's too slow, u can use prefetch to speed up.



> ....... which I think is right but I can;t get at all the rows from the
> returned resultset. If I return a list, it works. $group_rs->next() in a loop
> just returns the first row.
> 
> ## this works in ist context
> foreach my $group ( @groups ) {
>     my $group_name  = $group->get_column('group_name');
>     my $cat_name    = $group->get_column('category_name');
>     my $subcat_name = $group->get_column('subcategory_name');
>     my $prod_name   = $group->get_column('product_name');
>     print "$group $group_name => $cat_name => $subcat_name =>
> $prod_name\n";    
> }
> 
> =pod    
>  ## this doesn't work for all rows context                                
>                      
> foreach my $group ( $group_rs->next() ) {
>     my $group_name  = $group->get_column('group_name');
>     my $cat_name    = $group->get_column('category_name');
>     my $subcat_name = $group->get_column('subcategory_name');
>     my $prod_name   = $group->get_column('product_name');
>     print "$group $group_name => $cat_name => $subcat_name =>
> $prod_name\n";
> }
> =cut
> 
> I'd like to use the rs copntext and print something like .........
> 
> group1
>   cat1
>      subcat1
>          prod1
>           prod2
>          prod3
>       subcat2
>         prod4
>       subcat3
>          prod5
>   cat2
>      subcat4
>         ..... etc. etc.
> 
> Any tips would be greatly/warmly appreciated.
> 
> -Ants
> 
>  		
> ---------------------------------
>  Yahoo! Messenger - with free PC-PC calling and photo sharing.

-- 
FastJack

GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail