Is it possible to sort this SOQL statement in one query

apexquerysoqlsort

I have a list of records with name fields that all contain something like 'Name1', 'Name2, …, 'Name20'. It's also not guaranteed that there will be a complete list of 1-20, i.e. it can be a list of 1-13 at times.

I'm looking to sort these records by that Name field where they'll be listed from 1-20, however I'm having difficulty achieving it in one SOQL query.

[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name%' ORDER BY Name ASC]

This query results in an order that looks like 'Name10', 'Name11', 'Name12', 'Name13', 'Name1', 'Name2', … , 'Name9'.

I can do two SOQL statements to get single digit & double digit values, like so:

[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name_' ORDER BY Name ASC]
[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name__' ORDER BY Name ASC]

But this results in two lists that would then have to be concatenated together, which I was hoping to avoid.

Really I'm just hoping there's some secret sneaky way to accomplish this in one query. Any help with this would greatly be appreciated!

Best Answer

The ordering that looks like [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9] is called lexicographic ordering. When you have a mix of alpha and numeric characters, that's the reasonable approach to take (and how SOQL and many other systems handle it).

If you want numerical ordering ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, ...]), you'll need to do some additional work somewhere.

One approach you could take would be to create a formula field (returning a Decimal) to strip out the "Name" portion of the name, leaving you with just the numeric value. You could then ORDER BY <your formula field> to achieve the ordering you're looking for.

Related Solutions

[SalesForce] SOQL query not returning any record even when it is present. Trigger failing due to this

I observed the same issue in our full sandbox after a refresh. I believe it is a bug, though it is not yet on Salesforce's known issues list.

If you change your query to Select Id,Name from CaseTeamRole where Name != 'CSO', your query will probably return all results except the record where the Name is CSO.

If you change your query to Select Id,Name from CaseTeamRole where Name like '%s%', your query will probably return two records: CSO and Global Sales Manager.

It looks to me that the results only return if > 1 row is in the result set.

The workaround that could work for you is to query for all CaseTeamRole records and then loop through each result looking for Name = 'CSO'. For example:

for (CaseTeamRole ctr: [select id, name from CaseTeamRole])
{
 if(ctr.Name == 'CSO')
 {..do stuff.. }
}

If you happened to open a case with Salesforce, would be curious to know if they confirmed whether it is a bug or not.

[SalesForce] SOQL Query help with more than one level of relationships

You say that your RS object is in a Master-Detail relationship with Case. Assuming that RS is the detail side of the relationship, you simply need to loop over your RS records and gather the related Case Ids.

With that, you should be able to run your query with one small change.

// Declare either a set or a list to hold the Ids.
// It doesn't really matter which one, my preference is for a Set though.
Set<Id> caseIdsSet = new Set<Id>();
for(RS__c rsRec :myRsRecordList){
    // I'm assuming the relationship field is Case__c
    // Even though the relationship field will show you the name of the related
    //   record in Visualforce (guessing the same is true for Lightning), the field, in
    //   reality, holds the Id of the related record.
    caseIdsSet.add(rsRec.Case__c);
}

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN (SELECT AccountId
                                                  FROM Case
                                                  // This is the small change to your query
                                                  WHERE Id IN :caseIdsSet
                                                 )];

If this code is part of a trigger on your RS object, then this is the only query that you'll need. If you're not doing this as part of a trigger (or otherwise don't have access to the Trigger context variables), then you will need another query to grab the required field(s) from your object.

+edit:

So you're outside of a trigger context. In that case, there's only one way I can think of the avoid the second query; use a formula field.

Update to edit:

formula fields can't be used in semijoins. There is still merit in the example below, but to use it in your situation, you'd still likely need to query RS__c records to obtain the value of the formula field (and then you'd be using code based on my first example).

If you have a formula field that pulls down the AccountId from the Case your RS record is related to, you could use that in your semi-join like so...

List<Contact> contactsList = [SELECT Name, Id
                              FROM Contact
                              WHERE AccountId IN
                                  (SELECT Account_Id_Formula__c 
                                   FROM RS__c
                                   WHERE Id IN :myRSRecordsList
                                  )
                          ];

While the most common use of a semijoin is where you'd query the object whose Ids you're trying to obtain (i.e. Account in this case), it'll work on any object provided that you have a field that holds the appropriate Id on that object (I don't believe you're allowed any hierarchy traversal, up or down, in a semijoin).