Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Merging Related Cats + Categories

Hi,

I've been trying to work this out for almost 4 days now, and am still not really anywhere near as realistic solution :(

Basically, I want to use the following kind of code:

Code:
   my $tbl = $DB->table('Category');  
      $tbl->select_options('ORDER BY Name ASC'); 
   my $topcats = $tbl->select( { FatherID => $_[0] } ); 

   my $rows = $topcats->rows || 0; 

   my $i = 0; 
   while (my $hit = $topcats->fetchrow_hashref) {  
     push @cats, $hit; 
   }

..but also do the Related categories into this same loop - with something like:

Code:
   my $related_db = $DB->table('CatRelations'); 
   my $cat_db     = $DB->table('Category'); 
   my $opts = undef; 
   my %related_ids = $related_db->select(qw/RelatedID RelationName/ => { CategoryID => $_[0] })->fetchall_list; 

   my @cats; 
   if (keys %related_ids) { 
   	 $cat_db->select_options("ORDER BY $opts->{cat_sb} $opts->{cat_so}") if $opts->{cat_sb}; 
         my $sth = $cat_db->select({ ID => [keys %related_ids] }); 
         while (my $cat = $sth->fetchrow_hashref) { 
	    my $url = $CFG->{build_root_url} . "/" . $cat_db->as_url($cat->{Full_Name}) . "/" . $CFG->{build_index}; 
            $cat->{URL} = $url; 
	    $cat->{Related} = 1; 
            push @cats, $cat; 
	 }   
    }

As you can see, getting the hash's into the @cats array isn't the problem. The problem comes when I try and "sort" them (because you can't just do a simple MySQL ORDER BY statement, as the tables are totally separate).

Am I missing something really silly here? Any suggestions?

TIA!

Andy (mod)
andy@ultranerds.co.uk

Hi,

Never mind, I managed to work something out Smile

For anyone interested, this will give you an array of the categories + related categories, from a given CategoryID;

Code:
   my $cats_list; #hashref 
   my @cats; 

    my $tbl = $DB->table('Category');   
      $tbl->select_options('ORDER BY Name ASC');  
    my $topcats = $tbl->select( { FatherID => $_[0] } );  

    my $rows = $topcats->rows || 0;  

    while (my $hit = $topcats->fetchrow_hashref) {   
      $cats_list->{$hit->{Name}} = $hit;  
    } 

   my $related_db = $DB->table('CatRelations');  
   my $cat_db     = $DB->table('Category');  
   my $opts = undef;  
   my %related_ids = $related_db->select(qw/RelatedID RelationName/ => { CategoryID => $_[0] })->fetchall_list;  

   my @cats;  
   if (keys %related_ids) {  
   	 $cat_db->select_options("ORDER BY $opts->{cat_sb} $opts->{cat_so}") if $opts->{cat_sb};  
         my $sth = $cat_db->select({ ID => [keys %related_ids] });  
         while (my $cat = $sth->fetchrow_hashref) {  
 	     my $url = $CFG->{build_root_url} . "/" . $cat_db->as_url($cat->{Full_Name}) . "/" . $CFG->{build_index};  
            $cat->{URL} = $url;  
            $cat->{Name} = $cat->{Name};  
     	    $cat->{Related} = 1;  
            $cats_list->{$cat->{Name}} = $cat;  
	 }    
    } 

   map { 
     push @cats, $cats_list->{$_}; 
   } sort keys %$cats_list;

Then its just a case of:

Code:
  foreach my $cat (@cats) { 
    # do what you want here - has all  
    # values of categories, ie $cat->{Name} , 
    # $cat->{URL} etc 
  }

Cheers

Andy (mod)
andy@ultranerds.co.uk

Merging Related Cats + Categories - in loop