Gossamer Forum: Products: Gossamer Links: Discussions: SQL to select all LINKS records under a specific Category tree branch

Aug 18, 2005, 5:57 PM

vicos

Novice (9 posts)

Aug 18, 2005, 5:57 PM

Post #1 of 3

Shortcut

SQL to select all LINKS records under a specific Category tree branch

Does anyone know the SQL statement to select all LINKS records under a specific category tree branch?

For example, given the following category tree:

A
----B
---------BA
---------BB
--------------------BBA
--------------------BBB
C
D

I need to find all Links under the "B" Branch, i.e. ALL LINKS IN THE FOLLOWING CATEGORIES: B, BA, BB, BBA, BBB

A comprehensive ERM diagram would also help...

Aug 19, 2005, 12:06 AM

Andy

Veteran / Moderator (18441 posts)

Aug 19, 2005, 12:06 AM

Post #2 of 3

Shortcut

Re: [vicos] SQL to select all LINKS records under a specific Category tree branch In reply to

Hi,

Something like this should work (not a MySQL statement I'm afraid) - NB, this needs to obviously be a global =)

Code:
sub { 

  my $cat_id = $_[0] || 1; 

  my @cat_ids; 
  my $child = $DB->table('Category')->children($cat_id); 
     push @cat_ids, @$child, $cat_id;    
  } 

  my $tbl = $DB->table('CatLinks'); 
  my $sth = $tbl->select( GT::SQL::Condition->new('CategoryID','IN',\@cat_ids)  ); 

  my @links; 
  while (my $hit = $sth->fetchrow_hashref) { 
     my $link = $DB->table('Links')->get($hit->{LinkID});        
          $link = Links::SiteHTML::tags('link', $link); 
      push @links, $link; 
  } 

  return { loop_links_new => \@links }; 

}

Call with;

Code:
<%global_name('OPTIONal_ID')%> 
<%if loop_links_new%> 
 <%loop loop_links_new%> 
   <%include link.html%> 
 <%endloop%> 
<%endif%>

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Aug 19, 2005, 9:06 AM

vicos

Novice (9 posts)

Aug 19, 2005, 9:06 AM

Post #3 of 3

Shortcut

Re: [Andy] SQL to select all LINKS records under a specific Category tree branch In reply to

The following should work. Unfortunately, my version of mySQL does not support the specified subquery...

SELECT DISTINCT
`ID`, `Title`, `Contact_Name`, `Contact_Email`, `Address1`, `Address2`, `City`, `State`, `ZipCode`, `Phone1` `public_email`
FROM
lsql_Links, lsql_CatLinks
WHERE
lsql_CatLinks.LinkID=lsql_Links.ID
AND lsql_CatLinks.CategoryID IN ( Select ID from lsql_Category where CatRoot=? )
ORDER BY lsql_Links.Title