Hello,



Could someone tell me what the SQL command would be to get all the category IDs that are under a certain category level? I was thinking this would be easy in the SQL monitor if I new SQL. Thank you

Do you need just the actual sql command or the code to work with links sql?

I am trying to get this global to work with all the subcats of a specific category:

sub {
my $tags = shift;
my $limit = ($tags->{Random_Limit} =~ /^(\d+)$/) ? $1 : 3;
my @parents = $IN->param('Random_CatID');
my @cat_ids; foreach my $id (@parents)
{
push @cat_ids, $id, $DB->table('Category')->children($id);
}
my $link_db = $DB->table('Links','CatLinks');
$link_db->select_options ('ORDER BY ADD_DATE DESC', "LIMIT $limit");
my $condition = GT::SQL::Condition->new( 'isNew', '=', 'Yes','isValidated','=','Yes');
if (@cat_ids) {
$condition->add('CategoryID', 'IN', \@cat_ids);
}
my $sth = $link_db->select($condition);
my @output;
while (my $hash = $sth->fetchrow_hashref) {
push @output, $hash;
}
return { Random_Loop => \@output } }

My problem is that I have hundreds of subcats in this specific root cat and I do not want to hard code them when I call the global.

Does this make sense Paul?

For just the SQL it would be:

SELECT ID FROM lsql_Category WHERE Full_Name LIKE 'Cars/%'

to find all ID's of categories beneath Cars.

Cheers,

Alex
--
Gossamer Threads Inc.

Thank you Alex. Off course the full name starts the same way for each main category.. Nice one.

Okay here is my global with the new SQL condition:

sub {
my $tags = shift;
my $limit = ($tags->{Random_Limit} =~ /^(\d+)$/) ? $1 : 10;
my @parents = $IN->param('Random_CatID');
my @cat_ids; foreach my $id (@parents)
{
push @cat_ids, $id, $DB->table('Category')->children($id);
}
my $link_db = $DB->table('Links','CatLinks','Category');
$link_db->select_options ('ORDER BY ADD_DATE', "LIMIT $limit");
my $condition = GT::SQL::Condition->new('WHERE', 'Full_Name', 'LIKE', 'Images', 'isNew', '=', 'Yes','isValidated','=','Yes');
if (@cat_ids) {
$condition->add('CategoryID', 'IN', \@cat_ids);
}
my $sth = $link_db->select($condition);
my @output;
while (my $hash = $sth->fetchrow_hashref) {
push @output, $hash;
}
return { Random_Loop => \@output } }



I am getting the following: unable to compile ... Could someone give this a look please. Thank you

This:

GT::SQL::Condition->new('WHERE', 'Full_Name', 'LIKE', 'Images', 'isNew', '=', 'Yes','isValidated','=','Yes');

is wrong. It needs to be:

GT::SQL::Condition->new('Full_Name', 'LIKE', 'Images/%', 'isNew', '=', 'Yes','isValidated','=','Yes');

However, I'm not sure what you are trying to do as:

$condition->add('CategoryID', 'IN', \@cat_ids);

should already add into the condition that the link must appear in one of the subcategories.

Cheers,

Alex
--
Gossamer Threads Inc.

Alex,

I think that the problem is with

push @cat_ids, $id, $DB->table('Category')->children($id);

as children($id) gives an array ref.

I think it should be something like this?

my $child_ids = $DB->table('Category')->children($id);

push @cat_ids, $id, @$child_ids;


Laura.
The UK High Street