<p>ok i found the solution. something about craft that i am (slowly) learning is that when i access elements via something like, e.g., <code>craft()-&gt;globals-&gt;getSetByHandle('stockists')</code> i'm not getting raw data as such, i am getting an ElementCriteriaModel object! thus my solution is:</p>

<pre><code>$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();
</code></pre>

<p>where <code>GeocodeCalcDistance</code> is my stored function.</p>

<p>et voila!</p>

<h3>ANOTHER SOLUTION</h3>

<p>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 <code>having</code> 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 <a href="https://craftcms.stackexchange.com/a/10878/3789">here</a>, which gave me some inspiration.</p>

<p>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 <code>having</code> clause. this is done by constructing an "initial" element query (based on a typical <code>ElementCriteriaModel</code> query), then turning it into a <code>DbCommand</code> object by using <code>craft()-&gt;elements-&gt;buildElementsQuery()</code>. then i can tweak that query, execute it, then populate models with the raw data returned.</p>

<pre><code>$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);
}
</code></pre>

CC BY-SA 3.0
2015-11-28T00:11:03.203Z
2015-11-28T15:38:20.440Z
2017-04-13T12:50:59.930Z
bhu Boue vidya
<p>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:</p>

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

<p>where <code>GeocodeCalcDistance()</code> is my stored function. I have tested this sql and it works.</p>

<p>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:</p>

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

<p><strong>but</strong> I don't have a "record" class for the table data. I am pretty stumped. any clues greatly appreciated.</p>

CC BY-SA 3.0
2015-11-27T16:02:06.307Z
Don't have time to flesh out a proper answer right now, but you'd definitely want to use query builder (DbCommand - `craft()->db->createCommand()`) instead of ActiveRecord to build the query.
Brad Bell
CC BY-SA 3.0
2015-11-27T19:15:57.627Z
This might help: http://craftcms.stackexchange.com/questions/10875/filter-locations-by-country-in-smart-map/10878#10878
carlcs
CC BY-SA 3.0
2015-11-29T11:13:55.003Z
hey @carlcs thx for the tip. that is an awsm technique you outline for tweaking element queries. i used it as inspiration for "Another Solution" as outlined in my answer below. it has opened up a whole world of querying possibilities for me - thx heaps!
bhu Boue vidya
<plugin-development>
Query a Supertable using MySQL Stored Function
# Query a Supertable using MySQL Stored Function

(Asked by: username_0 on 2015-11-27)

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' => value1);
$stockists = RecordClass::model()->findAll($criteria, $params);

but I don't have a "record" class for the table data. I am pretty stumped. any clues greatly appreciated.

**Tags:** plugin-development

> **Comments:**
> **username_1**: Don't have time to flesh out a proper answer right now, but you'd definitely want to use query builder (DbCommand - `craft()->db->createCommand()`) instead of ActiveRecord to build the query.  
> **username_2**: This might help: http://craftcms.stackexchange.com/questions/10875/filter-locations-by-country-in-smart-map/10878#10878  
> **username_0**: hey @username_2 thx for the tip. that is an awsm technique you outline for tweaking element queries. i used it as inspiration for "Another Solution" as outlined in my answer below. it has opened up a whole world of querying possibilities for me - thx heaps!  

---

## 1 answers

### Answer by username_0 on 2015-11-28. Score: 2 | Accepted answer

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()->globals->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()
    ->globals
    ->getSetByHandle('stockists')
    ->stockistList
    ->order(sprintf('(GeocodeCalcDistance(lat, lng, %f, %f, "km")) asc', $target_lat, $target_lng))
    ->limit($limit)
    ->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()->elements->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()->elements->buildElementsQuery(
    craft()->globals
        ->getSetByHandle('stockists')
        ->stockistList
        ->order(sprintf('dist asc', $geocode->lat, $geocode->lng))
        ->limit($limit)
    );

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


---

