6

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.

3

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.

  • I tried formatting the request that way but still received the same error. :( – KB145 Aug 19 '15 at 15:49
  • I've updated my answer. – Boris Bachovski Aug 19 '15 at 23:36
  • 1
    I was hoping that relationship traversing was supported since it's not technically the same as a bind variable, but apparently both are not supported at this time. However, ScottW was also correct in stating that you don't have to use GEOLOCATION as the second parameter for the DISTANCE filter, you can use two location fields, but you can't use a location field that you obtain by traversing a parent-child relationship. – KB145 Aug 21 '15 at 15:37
1

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
]; 
  • That was actually what I tried first and unfortunately it didn't work :(. Seems like it should though, right? – KB145 Aug 19 '15 at 20:47
  • Unfortunately it won't work if your second location field parameter is obtained by traversing a parent-child relationship, but you are right that GEOLOCATION is not necessary, you can use two location fields as the DISTANCE parameters. – KB145 Aug 21 '15 at 15:39
0

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

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.