Query a Supertable using MySQL Stored Function

Question

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: http://craftcms.stackexchange.com/questions/10875/filter-locations-by-country-in-smart-map/10878#10878 — 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! — bhu Boue vidya, Nov 29 '15 at 11:13

score 2 · Accepted Answer · edited Apr 13 '17 at 12:50

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

Query a Supertable using MySQL Stored Function

1 Answers1

ANOTHER SOLUTION