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.
craft()->db->createCommand()
) instead of ActiveRecord to build the query. – Brad Bell Nov 27 '15 at 16:02