1

What's the quickest way to add field values to a set? Currently, doing it in the following way

Set<String> accountNames = new Set<String>();

for(Account acc : [Select Id, Name from Account LIMIT 50000]){
  accountNames.add(acc.Name)
}

Is there a simpler way of doing it?

2 Answers 2

3

This not well-known trick can solve the problem in one line

 Set<String> accountNames = new Map<String,SObject>(
    [SELECT Name Id FROM Account GROUP BY Name]).keySet();

From the Matt and Neil blog post

How it works

The first thing to note is that we are using an Aggregate Query and not just ordinary SOQL. We combine special behaviours available to aggregate queries to build the set:

Step 1 - GROUP BY to collect unique values Exactly per the documentation, you can use the GROUP BY option in a SOQL query to avoid iterating through individual query results. That is, you specify a group of records instead of processing many individual records.

Step 2 - Alias the field as Id The default identifier for an aggregated field is expr0 but we will alias the result as Id. It doesn't matter what the field contains, so long as it's a string. We will exploit this special alias in the next step.

Step 3 - AggregateResult inherits from sObject The methods available on sObject are also available on AggregateResult. This also applies to Lists and Maps; those same methods can be called on collections of aggregate results. Here is another example using keySet()

Set<String> emails = new Map<String,SObject>(
    [SELECT Email Id FROM Contact GROUP BY Email]).keySet();
2
  • Very cool! And quite bizarre that this works since that implies the Map constructor fakes out SObjects from aggregate results (well, as you said, there is some inheritance going on)... I will certainly look see if I can find places to make use of this...
    – Phil W
    Dec 15, 2022 at 21:29
  • 1
    I agree. Super slick. One small difference between this solution and the OP's original code: given 3 accounts, 'Acme', 'ACME', and 'acme', the OP's code will result in a Set (case sensitive) of 3 names, but a SOQL query will return just one. Account.Name is required, so I think it wouldn't be possible to create those 3 accounts, but just to keep in mind in general... don't forget case sensitivity issues.
    – Steve Cox
    Jan 2, 2023 at 18:04
2

What you have is specifically the best way to deal with most such scenarios:

  1. Using the SOQL for loop approach allows runtime to be efficient with heap space.
  2. Adding detail to the account names set only in that loop makes the loop as performant as possible.

In this specific case you are after unique account names only, so you can switch to using an aggregate query with grouping:

Set<String> accountNames = new Set<String>();

for (AggregateResult acc : [SELECT Name FROM Account GROUP BY Name LIMIT 50000]) {
    accountNames.add((String) acc.get('Name'));
}

This may be slightly faster if you do have significant numbers of duplicate Account names. If you don't, then your existing solution is likely to be slightly faster (based on some rather trivial testing with small numbers of Accounts).

A general note: make sure you only query those fields you actually need since this can have an impact on performance.

PS: There's currently no API for turning that queried array of Accounts into a set of account Names (there's only a means to turn an array of Accounts into a set of IDs via Set<Id> ids = new Map<String, Account>(accounts).keySet()).

PPS: See Cropredy's answer for something approaching the API I said doesn't exist...!

2
  • 2
    note my answer for your toolkit; I learned this from sfdcfox and/or bigassforce but often forget it (the old "alias a grouped string field by Id trick")
    – cropredy
    Dec 15, 2022 at 20:41
  • 1
    lots of other good stuff at xbaf.com
    – cropredy
    Dec 15, 2022 at 22:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .