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.

  • 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
    Nov 27 '15 at 16:02
  • This might help: craftcms.stackexchange.com/questions/10875/…
    – carlcs
    Nov 27 '15 at 19:15
  • 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! Nov 29 '15 at 11:13

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()
    ->order(sprintf('(GeocodeCalcDistance(lat, lng, %f, %f, "km")) asc', $target_lat, $target_lng))

where GeocodeCalcDistance is my stored function.

et voila!


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(
        ->order(sprintf('dist asc', $geocode->lat, $geocode->lng))

$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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.