I've had to manually do an SQL call to the get correct results but I was hoping there would be a tidier way of achieving this in Craft 3?

$query = ( new Query() )
->select('craft_content.title, craft_entries.id, ( 3959 * acos( cos( radians(' . $latitude . ') ) * cos( radians( field_clubLatitude ) ) * 
            cos( radians( field_clubLongitude ) - radians(' . $longitude . ') ) + sin( radians(' . $latitude . ') ) * 
            sin( radians( field_clubLatitude ) ) ) ) AS distance')
->from( 'craft_elements' )
->where('field_clubLongitude != "" and field_clubLatitude != ""')
->join( 'LEFT JOIN', 'craft_entries', 'craft_elements.id = craft_entries.id' )
->join( 'LEFT JOIN', 'craft_content', 'craft_elements.id = craft_content.elementId' )
->andHaving('distance < 100')
  • Could you please explain what you want to achieve in detail? Should every query have these additional selects? Do you need it only once? Do you need system wide queries? Do you only need those additions when you query or should other team members be able to attach them with craft.entries too? The most easiest way is to create a custom query class that extends the entries query and use that in your twig template. Thus you would do craft.plugin.entries to return a custom query class Feb 8 '18 at 16:06

The "proper" way to access the element query that gets generated is through a plugin via the ElementQuery's query and subquery properties.

It looks like you're doing a Haversine algorithm to calculate distance between a latitude and longitude. There's are some similar plugins that already do this you can use as an example.

For example: https://github.com/ethercreative/simplemap/blob/v3/src/services/MapService.php

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.