SOQL Query to retrieve Contacts from an Account sorted by the distance between the contact location and the account location

Question

I'm trying to create a SOQL query to retrieve Contacts from Accounts, with the contacts for each account sorted by the distance between the contact location and the account location.

This is what I have so far, but when I try to run it, I get an error stating

"unexpected token: 'account.locationField__c'".

Any ideas on what I'm doing wrong or how I can restructure this to get it to work?

Thanks!

List<Account> accounts = [
    SELECT Id, Account.locationField__c, (
        SELECT Id, accountId, Contact.locationField__c
        FROM Contacts 
        WHERE RecordType.Name = 'Customers' 
        ORDER BY DISTANCE(Contact.locationField__c, Account.locationField__c, 'mi')
    ) 
    FROM Account
]; 

Also I've tried using a GEOLOCATION and it does not work with the relationship fields but it works fine with static numbers:

Works! (Static coordinates)

List<Account> accounts = [
    SELECT Id, Account.locationField__c, (
        SELECT Id, accountId, Contact.locationField__c
        FROM Contacts 
        WHERE RecordType.Name = 'Customers' 
        ORDER BY DISTANCE(
            Contact.locationField__c, 
            GEOLOCATION(33.153, -112.616), 
            'mi'
        )
    ) 
    FROM Account
]; 

Does NOT work! (Account Relationship fields)

List<Account> accounts = [
    SELECT Id, Account.locationField__c, (
        SELECT Id, accountId, Contact.locationField__c
        FROM Contacts 
        WHERE RecordType.Name = 'Customers' 
        ORDER BY DISTANCE(
            Contact.locationField__c, 
            GEOLOCATION(Account.latitudeField__c, Account.longitudeField__c), 
            'mi'
        )
    ) 
    FROM Account
]; 

I've also referenced the relationship field like so:

Contact.Account.CustomFields

Outside the context of DISTANCE/GEOLOCATION this relationship statements work fine but within it they return as an unexpected token.

Answer 1

The second parameter in the DISTANCE function is GEOLOCATION (with latitude and longitude parameters). Your query should look like this:

ORDER BY DISTANCE(locationField__c, GEOLOCATION(account.Location__latitude__s, account.Location__longitude__s), 'km')

UPDATE:

Apparently

Apex bind variables aren’t supported for the units parameter in DISTANCE or GEOLOCATION functions.

states this doc at the bottom. You'll have to hard-code the latitude and longitude parameters in the GEOLOCATION function.

Answer 2

According to the documentation you can use two location fields, it does not necessarily need the GEOLOCATION function.

Usage: DISTANCE(mylocation1, mylocation2, 'unit') and replace mylocation1 and mylocation2 with two location fields, or a location field and a value returned by the GEOLOCATION function. Replace unit with mi (miles) or km (kilometers).

That being said, I think the extra alias on your first line may be messing it up. Try the following

List<Account> accounts = [
    SELECT Id, locationField__c, (
        SELECT Id, AccountId, locationField__c
        FROM Contacts 
        WHERE RecordType.Name = 'Customers' 
        ORDER BY DISTANCE(locationField__c, Account.locationField__c, 'mi')
    ) 
    FROM Account
]; 

Answer 3

For anyone who may see this post in the future, apparently the Spring '16 release will support the use of Geolocation as a param in the Distance function as shown in Boris' answer.

https://developer.salesforce.com/releases/release/Spring_%252716/distance