SOQL order by distance internal server error

Question

I am trying to add an ORDER BY statement in a SOQL query. I am already using the same statement in the WHERE clause of the query, but am getting an error saving it.

An unexpected error occurred. Please include this ErrorId if you contact support: 973471439-38882 (132416824)

I'm not sure if this is a platform problem or syntax problem. I am thinking it's a platform problem based on this post as I am working out of a Spring 18 sandbox. Oddly, I am able to run the query in that post though.

Query

SELECT Id, StartDate, EndDate, Start_Date_Time__c, End_Date_Time__c, Location__r.BillingStreet, Location__r.BillingCity, Maximum_Participants__c,
    Location__r.BillingState, Location__r.BillingPostalCode, Location__r.Name,
    (
        SELECT Id
        FROM CampaignMembers
        WHERE Status != 'Cancelled'
    )
FROM Campaign
WHERE StartDate >= :req.startDate.date()
    AND EndDate <= :req.endDate.date()
    AND DISTANCE(Location__r.BillingAddress, GEOLOCATION(:req.lat, :req.lng), 'mi') < :req.miles
    AND RecordType.DeveloperName = :SEMINAR_RECORD_TYPE
    AND Available__c = true
    AND StartDate >= :Date.today()
ORDER BY StartDate ASC, DISTANCE(Location__r.BillingAddress, GEOLOCATION(:req.lat, :req.lng), 'mi') ASC

The lat and long values are coming from the method parameters in a model object.

Model:

public class SearchModel {
    public String campaignId;
    public Integer miles;
    public String zip;
    public DateTime startDate;
    public DateTime endDate;
    public Decimal lat;
    public Decimal lng;
    public String consultationOrSeminar;
    public String productInterest;
}

Answer 1

Just to close the loop here, this behavior (not being able to bind variables in an ORDER BY clause) is documented in the Apex Developer Guide, though I didn't think to look there earlier. Emphasis mine:

Using Apex Variables in SOQL and SOSL Queries

SOQL and SOSL statements in Apex can reference Apex code variables and expressions if they’re preceded by a colon (:). This use of a local code variable within a SOQL or SOSL statement is called a bind. The Apex parser first evaluates the local variable in code context before executing the SOQL or SOSL statement. Bind expressions can be used as:

• The search string in FIND clauses.
• The filter literals in WHERE clauses.
• The value of the IN or NOT IN operator in WHERE clauses, allowing filtering on a dynamic set of values. Note that this is of particular use with a list of IDs or Strings, though it works with lists of any type.
• The division names in WITH DIVISION clauses.
• The numeric value in LIMIT clauses.
• The numeric value in OFFSET clauses.

Bind expressions can't be used with other clauses, such as INCLUDES.

Answer 2

Received a response from Salesforce:

Good Morning !

I have received an update from my tier -3 stating that "Binding variables are NOT allowed in ORDER BY clause, hence adding the following piece of code in an apex thrown Internal Server Error " DISTANCE(Location__r.BillingAddress, GEOLOCATION(:req.lat, :req.lng), 'mi') ASC.

However, adding something like the following works: ORDER BY StartDate ASC, DISTANCE(Location__r.BillingAddress, GEOLOCATION(43.075861, -87.882772), 'mi') ASC

There is already an idea about this: https://success.salesforce.com/ideaView?id=08730000000l56WAAQ

Please let me know in case of any queries.

Work around:

Convert the soql to a dynamic soql string and execute using Database.query();

Answer 3

Perhaps write a string query:

string processQuery = 'Select  User.FirstName, User.LastName, 
ProfileSkill.Name, EFX_Skill_assessment__c 
FROM ProfileSkillUser WHERE ProfileSkill.Name LIKE \'%' + skill+ '%\'';

DataBase.query(processQuery);

And put your lat and long values into the string before execution.

This approach is mentioned here:

https://developer.salesforce.com/forums/?id=906F0000000BHdKIAW

And here

https://developer.salesforce.com/forums/?id=906F00000008ybqIAA