Changeset 318


Ignore:
Timestamp:
Jun 1, 2006, 6:24:12 PM (13 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)

File:
1 edited

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};
Note: See TracChangeset for help on using the changeset viewer.