Query a Supertable using MySQL Stored Function I have a supertable which lists stockists. Each stockist has a lat/lng for map location. On a page of the site i am implementing a map locator which will need to make an ajax request for the 10 closest stockists to a user supplied address. Basically I will need to query the supertable of stockists (in a plugin) to find these 10 closest. I have written a MySQL stored function to calculate distance between two pairs of lat/lngs. If I was doing raw sql on the database, the query would, for example, look like:

SELECT * FROM `craft_supertablecontent_stockistlist`
order by (GeocodeCalcDistance(field_lat, field_lng, -27, 153, "km")) ASC
limit 10


where GeocodeCalcDistance() is my stored function. I have tested this sql and it works.

Now to tranlate that into craft code! My stockists are stored in a supertable, which is defined wholly in the CP (i.e., I don't have custom Model/Record classes in a plugin for the data). How do I express this query in Craft logic? Usually you could do something along the lines of:

$criteria = '....';
$params = array('param1' =&gt; value1);
$stockists = RecordClass::model()-&gt;findAll($criteria, $params);


but I don't have a "record" class for the table data. I am pretty stumped. any clues greatly appreciated.
ok i found the solution. something about craft that i am (slowly) learning is that when i access elements via something like, e.g., craft()-&gt;globals-&gt;getSetByHandle('stockists') i'm not getting raw data as such, i am getting an ElementCriteriaModel object! thus my solution is:

$target_lat = ...;
$target_lng = ...;
$limit = ...;

$stockists = craft()
    -&gt;globals
    -&gt;getSetByHandle('stockists')
    -&gt;stockistList
    -&gt;order(sprintf('(GeocodeCalcDistance(lat, lng, %f, %f, "km")) asc', $target_lat, $target_lng))
    -&gt;limit($limit)
    -&gt;find();


where GeocodeCalcDistance is my stored function.

et voila!

ANOTHER SOLUTION

ok so what i did above was fine. but then i wanted to limit my results by the distance from the target location. this meant i needed the distance calc to move into the select clause, then i could reference it in a having clause. sounds a bit daunting to try and include in an element-based query. but then i came across a great answer to another question here, which gave me some inspiration.

so, i need to inject a new column into the select clause (the new column being a call to a stored function). then i need to reference that column in a having clause. this is done by constructing an "initial" element query (based on a typical ElementCriteriaModel query), then turning it into a DbCommand object by using craft()-&gt;elements-&gt;buildElementsQuery(). then i can tweak that query, execute it, then populate models with the raw data returned.

$target_lat = ...;
$target_lng = ...;
$limit = ...;
$range = ...;  // results have to be within this distance from the target

$query = craft()-&gt;elements-&gt;buildElementsQuery(
    craft()-&gt;globals
        -&gt;getSetByHandle('stockists')
        -&gt;stockistList
        -&gt;order(sprintf('dist asc', $geocode-&gt;lat, $geocode-&gt;lng))
        -&gt;limit($limit)
    );

$query-&gt;select(sprintf('(GeocodeCalcDistance(field_lat, field_lng, %f, %f, "km")) as dist, ', $geocode-&gt;lat, $geocode-&gt;lng) . $query-&gt;select);
$query-&gt;having(sprintf('dist &lt;= %d', $range));
if ($results = $query-&gt;queryAll()) {
    $stockists = SuperTable_BlockModel::populateModels($results);
}