Is there a way to create a custom ORDER BY in SOQL using Apex

apexsoql

Is it possible to make a custom ORDER BY?

SELECT Badge_Name__c, Session_Position__c FROM Attendee__c WHERE Event__c =: eventId ORDER BY Session_Position__c (???)

Session_Position__c is a picklist.

I'd like to display the return of that query like this in my visualforce page:

Moderator
Keynote Speaker
Special Guest
Co-chair
[A-Z] Badge Name

First Moderators, secondly Keynote Speakers, thirdly Special Guests and so on… so ASC or DESC doesn't fit in this scenario (only for the Badge Name, using ASC).

Any apex programming logic suggestions?

Best Answer

You could implement the comparable interface. While that wouldn't allow you to do the ordering in the query, it would allow you to use the sort() method that the List class provides.

Honestly though, this one is probably achieved easier by creating a simple formula field. The formula field would assign integer values to each value of your picklist, and you would assign integer values in a way that then allows you to use it in ORDER BY (either DESC or ASC).

Re-ordering the picklist values to be in your preferred order would probably be the simplest of all (picklist sort order in SOQL is determined by the order of picklist values as shown when you view the field in setup/the object manager), but that may not be an option for everyone and is susceptible to inadvertent meddling.

Related Solutions

[SalesForce] SOQL execution order

https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

In no particular order:

Don't believe, apply a debug log and experiment ;), fire off your batch and check the timestamps between SOQL_EXECUTE_BEGIN and SOQL_EXECUTE_END. Go to job monitoring, maybe the job spends almost 2 minutes in the preparing state (= running the initial querylocator)? As a rule of thumb I'd I believe should be similar and you should consider other factors.
Before thinking about performance think about your business logic. What are you doing with Contacts that don't have any permissions because of the WHERE applied? Just skipping them? Does your action match the approach 2 & two loops or maybe a flat list looks cleaner? I'm trying to say: make it easy to understand and maintain before doing performance improvements.
- Are you updating Contacts or Permissions in that batch or is it more of a readonly run through them? If you do update them - are you aware that you should ideally have FOR UPDATE slapped at the end of the query - but I'm not sure if in version 2 this will cascade down to subquery rows too and in version 1 - up to the contact...
- if you're in version 1 and updating the contact - does your logic handle the fact that a Contact with 5 permissions can be "sliced" so 2 Permissions end up in 1st execute() and remaining ones - in the next execute() chunk?
Are you aware that these waste different amount of query rows? In my Developer Edition I have 33 Contacts and 27 Accounts:
```
List<sObject> objs = [SELECT Id, Name, Account.Name FROM Contact]; // 33 rows
List<sObject> objs = [SELECT Name, (SELECT Id, Name FROM Contacts) FROM Account]; // 50 rows
// SELECT COUNT() FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact)
// returns 13
// SELECT COUNT() FROM Contact WHERE AccountId = null
// returns 10
```
So version 2 seems to waste all 33 + 27 rows even though there clearly are 10 contacts without account that shouldn't be included. Looks like your "some folks" might be right as counter-intuitive as that sounds.

With 50 K rows fetched limit the version 2 would use up 2000 * (1 contact + 5 permissions) = 16 K. That's not yet close to limit but can easily blow up.
Where does your list of Contact Ids come from? maybe you'd be better off pushing that query to start() or making more complex query that will save on the database roundtrips FROM Contact WHERE Account.Owner.isActive = true for example?
Do you know that version 2 can sometimes "lie" and result only first 100 rows from the related list for example? It's something similar to queryMore() mechanism from SOAP API.
What's the data skew? Does "TYPEA" account for say 40% of all rows? Is it always for that type? Could you consider having a workflow that copies picklist value to helper text field marked as external id (not unique, only ext id) and then filter on that helper field?

[SalesForce] way to maintain order in Map : Apex

Use the list to iterate and grab value from map. List is ordered and since map created from it you have the list already right

You said you created the map from a list. You have the ordered list. Use that to iterate and grab the mapped value on each iteration. Since using the list to iterate it will happen in the order of your list. Otherwise read: Iteration Order for Maps and Sets Is Now Predictable

The order of elements in unordered collections (Map and Set) is now the same each time your code is run. Previously, the order of elements in unordered collections was arbitrary, and you couldn’t rely on the order of elements in maps and sets.

Ideally, you don’t want your code to depend on the order of elements in unordered collections. If you have such code, it could sometimes fail before this change. This change makes your code more robust because the iteration order in maps and sets is always the same.

This example illustrates how the iteration order is deterministic in a map. A map is created with two elements. Next, the for loop iterates over the map elements and writes the key and value of each element to the debug log. Each time you execute this snippet, the iteration order of the elements in the for loop is the same.

Map<String, String> currencyMap = new Map<String, String>();
currencyMap.put('France','Euro');
currencyMap.put('Japan', 'Yen');
// Iterate through the map elements.
for (String mapKey : currencyMap.keySet()) {
    System.debug('Key: ' + mapKey +
        ', Value: ' + currencyMap.get(mapKey));
}

// The output is:
Key: France, Value: Euro
Key: Japan, Value: Yen

Best Answer

Related Solutions

[SalesForce] SOQL execution order

[SalesForce] way to maintain order in Map : Apex

Related Topic