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

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

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