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

query within search_results hook

Quote Reply
query within search_results hook
I am still trying to figure out how to alter $IN->param('query') wrt a search_results hook (PRE).

I can alter the query before calling sub search() by doing this:
$IN->param('query', '54720');

What I REALLY need to do is alter 'query' so that it holds an array of values as opposed to just one value.
Q: Is this even possible?

In my hook, I have
Code:
while (my $zip = $sth->fetchrow_hashref)
{
if(distance($olat, $olon, $zip->{Latitude}, $zip->{Longitude}, $unit) <= $dist)
{
push (@output, $zip);
}
}

...

return {\@output};

When finished, @output via #print GT::Dumper::Dumper(\@output); contains:
Code:
$VAR = [
{},
{
'Latitude' => '41.687414',
'Longitude' => '-86.979701',
'State' => 'IN',
'ZipCode' => '46301'
},
{
'Latitude' => '41.36959',
'Longitude' => '-87.44849',
'State' => 'IN',
'ZipCode' => '46303'
},

etc...

@output could easily contain only 'ZipCode' which is what I need to find.

So, I have an array of zipcodes that I want to search for.
Q: How do I put @output into a query?


This code snippet is from sub search() which is called at the end of my hook.
I could put @output into @args in my hook, then this routine would have my array in $args?
Code:
my $link_sth = $links->query_sth($args);

I found this snippet on the www. It seems that "join" may be what I am looking for.
I am still unsure how to do it though, or if it is even possible? GT?
Code:
my $query_sth;
$query_sth = "SELECT ".join(",",grep(/\S/,@rows))." FROM tester";
if (scalar (@values) > 0) {
$query_sth .= " WHERE ".join(",",grep(/\S/,@values));
};

$query_sth .= ';';

Thanks again for any help,
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] query within search_results hook In reply to
Update...

I added these 2 lines of code
Code:
my $where = join(" OR ", grep(/\S/,@output)) if @output;
$IN->param('args', $where);


Printing $where gives me...
$VAR = 'HASH(0x944c7f0) OR 53003 OR 53005 OR 53007 OR 53017 OR 53018 OR 53022 OR 53029 OR 53033 OR 53036 OR 53038 OR 53045 OR 53046 OR 53051 OR 53058 OR 53066 OR 53069 OR 53072 OR 53076 OR 53089 OR 53092' ;

Am I close?
I also tried $IN->param('query', $where);
But I dont think my $where is quite finished.

I think I need $where (the query) to be something like...
'Zip' = 53003 OR 'Zip' = 53005 OR 'Zip' = 53007

Sure would be nice to know if this will even work :-)

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] query within search_results hook In reply to
Well, I basically have Zip Code radius search working, but it is non-efficient and cpu intensive.
Someone please advise if there is a better way of doing this.
Please read my comments throughout the routine

Code:
sub search_results_hook {
# -----------------------------------------------------------------------------
# This subroutine will be called whenever the hook 'search_results_hook' is run. You
# should call GT::Plugins->action(STOP) if you don't want the regular
# 'search_results_hook' code to run, otherwise the code will continue as normal.
#
my $unit = 'M';
my $query = $IN->param('query');
my $dist = $IN->param('distance');

# Grab the ZipCode database
my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
# Find original (starting) ZipCode
my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;

if($osth)
{
# Get coordinates of start zip code
my $olat = $osth->{Latitude};
my $olon = $osth->{Longitude};

# Select the entire database 33.000 rows - probably bad
my $sth = $zipcode_db->select;
my @output;

# Cycle through zip codes comparing distances - while loop not good
# This finds ALL zipcodes within radius of start zip
# Possibly a big array of numbers depending on distance.
while (my $link = $sth->fetchrow_hashref)
{
# distance - standard routine for computing distances between 2 points
if(distance($olat,$olon,$link->{Latitude}, $link->{Longitude}, $unit) <= $dist)
{
# Looks like 'Hashref 12345, 12346, 12347, 12348 etc...' when finished,
# possibly several hundred items
push (@output, $link->{ZipCode});
}
}

# Remove commas, etc from array of Zipcodes
# The array of Zip Codes to search for is now '12345 12346 12347 12348' etc...
# This is ONLY the query for the Links Database, I still need to search that to find links whose
# zip codes that match any in the array. It uses an 'or' boolean on each zip code.
# In addition, it is searching all columns and not just zip code.
my $where = join(" ", grep(/\S/,@output)) if @output;
$IN->param('query', $where);

}

return search();
}

Now without the comments...
Code:
sub search_results_hook {
my $unit = 'M';
my $query = $IN->param('query');
my $dist = $IN->param('distance');
my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;

if($osth)
{
my $olat = $osth->{Latitude};
my $olon = $osth->{Longitude};
my $sth = $zipcode_db->select;
my @output;

while (my $link = $sth->fetchrow_hashref)
{
if(distance($olat,$olon,$link->{Latitude}, $link->{Longitude}, $unit) <= $dist)
{
push (@output, $link->{ZipCode});
}
}
my $where = join(" ", grep(/\S/,@output)) if @output;
$IN->param('query', $where);
}

return search();
}

Method 1 (current method)
Do I just need to clean this up? or is the method just wrong?
I am finding ALL zip codes within radius of start zip.
Then using that large array with an 'or' for the query.
Essentially just changing parameters and continuing with search()
Plus searching all fields.

Method 2 (not completed)
I had started doing this a different way... and I still have the code :-) Would this be a better method?
- Post hook on add/modify that puts longitude and latitude into Links table when links are added or modified.
- The while loop in the code above is exactly the same except it should be searching links db not zip db
- The difference is that when the while loop is done, I would have an array of the actual links themselves
- No need to do a search() at all. Could stop execution.

Caveats to method 2:
- Have to paginate results
- No grouping by category unless I do it
- When ZipCode database is updated, it will NOT update data in Links table.

I guess writting this out helps me decide that method 2 is probably the way to go.
Maybe a little more work as I have to 'finish' things up rather than just slamming GTs search() with a big array of words to do an 'or' search on. :-)

I still want feedback though please.

Peace,
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com