Changeset 318

Show
Ignore:
Timestamp:
06/01/06 18:24:12 (6 years ago)
Author:
nick
Message:

Refactor query as another inner join, so it's faster, and we can restrict ourselves easily to just the latest version (previously we could've matched on an old version's location)

Files:
1 modified

Legend:

Unmodified
Added
Removed

  • wiki-toolkit-plugin-locator-grid/trunk/lib/Wiki/Toolkit/Plugin/Locator/Grid.pm

    r274 r318  
    227227    # radius $distance.  The SELECT DISTINCT is needed because we might 
    228228    # have multiple versions in the table. 
    229     my $sql = "SELECT DISTINCT x.name FROM node AS x INNER JOIN metadata 
    230               AS mx ON (mx.node_id = x.id), node AS y INNER JOIN metadata my 
    231               ON (my.node_id = y.id) WHERE mx.metadata_type = '$self->{x}' 
    232                   AND my.metadata_type = '$self->{y}' 
    233                   AND mx.metadata_value >= " . ($sx - $metres) 
    234             . "   AND mx.metadata_value <= " . ($sx + $metres) 
    235             . "   AND my.metadata_value >= " . ($sy - $metres) 
    236             . "   AND my.metadata_value <= " . ($sy + $metres) 
    237             . "   AND x.name = y.name"; 
     229    my $sql = "SELECT DISTINCT x.name ". 
     230                          "FROM node AS x ". 
     231                          "INNER JOIN metadata AS mx ". 
     232                          "   ON (mx.node_id = x.id AND mx.version = x.version) ". 
     233                          "INNER JOIN node AS y ". 
     234                          "   ON (x.id = y.id) ". 
     235                          "INNER JOIN metadata my ". 
     236              "   ON (my.node_id = y.id AND my.version = y.version) ". 
     237                          " WHERE mx.metadata_type = '$self->{x}' ". 
     238              "   AND my.metadata_type = '$self->{y}' ". 
     239              "   AND mx.metadata_value >= " . ($sx - $metres) . 
     240              "   AND mx.metadata_value <= " . ($sx + $metres) . 
     241              "   AND my.metadata_value >= " . ($sy - $metres) . 
     242              "   AND my.metadata_value <= " . ($sy + $metres); 
    238243    $sql .= "     AND x.name != " . $dbh->quote($args{node}) 
    239244        if $args{node};