Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-28T00:11:03.203Z
Answers.LastActivityDate: 2015-11-28T15:38:20.440Z
Answers.LastEditDate: 2017-04-13T12:50:59.930Z
Answers.UserDisplayName: bhu Boue vidya
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-27T16:02:06.307Z
Comments.Text: 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.
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-27T19:15:57.627Z
Comments.Text: This might help: http://craftcms.stackexchange.com/questions/10875/filter-locations-by-country-in-smart-map/10878#10878
Comments.UserDisplayName: carlcs
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-29T11:13:55.003Z
Comments.Text: 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!
Comments.UserDisplayName: bhu Boue vidya
Tags: <plugin-development>
Title: Query a Supertable using MySQL Stored Function
ThreadText: # 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](https://craftcms.stackexchange.com/a/10878/3789), 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);
  }
  
  ```
  
  
  ---
  
  