Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Extract all values of field in cat and sub cats

Quote Reply
Extract all values of field in cat and sub cats
I'm sure this has been done before - but I can't find it.

Suppose each record has a field "Brand" in it.

I need a global to extract a list of all the values for brand in the current category, and all categories below.

It would be nice if the list was unique, with the number of occurences, but I can take care of that if I can just get them in some kind of list.

Thanks
Quote Reply
Re: [CrazyGuy] Extract all values of field in cat and sub cats In reply to
Hi,

Try something like this;

Code:
sub {

my $sth = $DB->table('Links')->select( ['DISTINCT(Brand)'] ) || return $GT::SQL::error;
my $results;
while (my $brand = $sth->fetchrow) {
$results->{$brand} = $DB->table('Links')->count( { Brand => $brand } );
}

my @results;
map {
my $tmp;
$tmp->{count} = $results->{$_};
$tmp->{name} = $_;
push @results, $tmp;
} keys %$results;

return { brand_loop => \@results }

}

Call with:

Code:
<%global_name%>
<%if brandloop%>
<%loop brand_loop%>
<%name%> has <%count%> listings.... <br/>
<%endloop%>
<%endif%>

NB: This is untested.. so no guarantees Tongue

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Extract all values of field in cat and sub cats In reply to
That doesn't take into account the current category id, it will just select all brands regardless of category.

You are also using map in a void context again :) ...it's not intended to be used like that.
Quote Reply
Re: [Hargreaves] Extract all values of field in cat and sub cats In reply to
Quote:
That doesn't take into account the current category id, it will just select all brands regardless of category.

Oops, missed that part Tongue

Quote:
You are also using map in a void context again :) ...it's not intended to be used like that.

Is your name Paul by any chance? Crazy

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Extract all values of field in cat and sub cats In reply to
Quote:
Is your name Paul by any chance

No, Pete Hargreaves. Just trying to share some advice, that's all. With warnings on you'll catch things like that.
Quote Reply
Re: [CrazyGuy] Extract all values of field in cat and sub cats In reply to
Well, I found some working global code that extracts data from current and child cats. From that, I got this snippet to create a list of the current and child category Ids:

my $catid = $_[0];
my $all_ids = $DB->table('Category')->children($catid);
push (@$all_ids, $catid);

And - although I understand in principle what I now need to do to get a distinct list of "Brands" from that list of categories in all_ids, I cannot get a Links-friendly global syntax that actually works.
Quote Reply
Re: [CrazyGuy] Extract all values of field in cat and sub cats In reply to
Haha

Funny - it works if I test in a category that actually *has* some values for "Brand" Blush

The formatting of the output is clunky, but that's because I was trying to debug what was happening, but this does work:

sub {
my $catid = $_[0];
my $all_ids = $DB->table('Category')->children($catid);
push (@$all_ids, $catid);
my $search_db = $DB->table('Links','CatLinks');
$search_db->select_options ('ORDER BY Brand DESC');
my $sth = $search_db->select (['DISTINCT(Brand)'], GT::SQL::Condition->new(['CategoryID', 'IN', $all_ids]));
my $results="";
while (my $thisbrand = $sth->fetchrow)
{
$results = "$results<BR>$thisbrand";
}
return $results;
}

One strange thing remains: if I remove DESC from "$search_db->select_options ('ORDER BY Brand DESC');" I should get a normal alphabetic sort, right?
Instead, I get nothing.