Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Re: [pugdog] Need help with Sort by distance: Edit Log

Ok, I will attempt the temporary table. I have a few questions right off the bat...

This 'temporary' table... is it a physical table that exists or is it temporary as in ram?
If it is a real table say named links_temp, and I actually copy data into it while doing a search, what prevents 2 concurrent searches from overwriting each others temp data?

I start here... This will give me a list of all zipcodes (from main ZipCode db) within the specified distance.

Code:
sub matching_zips {   
# -----------------------------------------------------------------------------   
#   
#   
    my ($query, $unit, $dist) = @_;   
    my @output;   

    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;   

        while (my $link = $sth->fetchrow_hashref)   
        {   
            my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);   

            if( $temp_distance <= $dist)   
            {   
                push (@output, $link->{ZipCode});   
            }   
        }   
    }   

    return @output;   

}

Easy enough... Now I select ALL links that match ANY of the Zip Codes from above

Code:
my @zips = matching_zips($query, $unitcode, $dist);     

    my $link_db = $DB->table('Links') || return $GT::SQL::error;     
    my $sth = $link_db->select(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

I need to dump all these links into a 'temp' table here and add a column for 'distance'.
I could use an Example Please :-)

Now cycle through these links filling in 'distance'. No comparison needed we know they are all within distance already.

Code:
        my $temp_db  = $DB->table('temp') || return $GT::SQL::error;  
         my $sth = $temp_db->select;
         while (my $link = $sth->fetchrow_hashref)  
        {  
            my $temp_distance = distance($olat, $olon, $link->{Latitude},$link->{Longitude}, $unit);  

 Update each row with $temp_distance????;
        }

Once temp table is completed, Do I still call 'query' to add the paging options?
or do I have to roll my own paging? I can add the toolbar, but don't understand how to grab the 'next'
page of links on a subsequent request for the next page.

This would work? If so I can finish from the end of this snippet onword

Code:
    my %filter = ( 
        mh    => $args->{mh}, 
        nh    => $args->{nh}, 
        sb    => $args->{sb},   # Can Now use Distance here 
        so    => $args->{so}, 
        ww    => 1 
    ); 
 
my $temp_db  = $DB->table('temp') || return $GT::SQL::error;  
my $temp_sth = $temp_db->query(\%filter);  
$link_count = $temp_db->hits;  
my $results = $temp_sth->fetchall_hashref;

Please let me know if this sounds right and fill in any blanks that you can :-)

Cacheing would probably be a good idea too :-)

Thanks
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com

Products: Gossamer Links: Development, Plugins and Globals: Re: [pugdog] Need help with Sort by distance: Edit Log

Here is the list of edits for this post

Last edited by:

Edit Log: