I learned something recently I want to share involving the Salesforce map function keySet(). As the name implies, it will return a set of keys from a map. This allows you to write more efficient code by avoiding looping over a map to extract the keys. In most use cases this would be used to get a set of IDs. However, did you know you can leverage keySet() with an aggregate SOQL query and a field alias to create a set consisting of a non–ID field (eg, email) or even a parent or grandparent ID field?
Huh?
Ok, I get it. Maybe that’s confusing. Or it doesn’t sound exciting or useful until you can actually see it in action. So here’s a quick example to give you an idea. Instead of writing a SOQL query and looping over it to get a set of Contact emails like this:
List<Contact> cons = [SELECT Id, Name, Email FROM Contact];
Set<String> conEmails = new Set<String>();
for (Contact con : cons) {
conEmails.add(con.Email);
}
You can take a shortcut like this:
Set<String> conEmails = new Map<String,SObject>([SELECT Email Id
FROM Contact
WHERE Email != null
GROUP BY Email]).keySet();
Pretty handy, right? So, to understand what’s happening above, let’s talk about maps first.
Like lists and sets, maps are a type of collection and they consist of key-value pairs. You can think of the key as unlocking the information behind the value. For this reason, each key must be unique. The values, on the other hand, can be duplicates (often they’re not, but they can be).
A typical key-value pair in Apex is Id:sObject. This is written as Map<Id, sObject>. For example, we can write the following code that initializes a map using the Account object:
Map<Id,Account> acctMap = new Map<Id,Account>([SELECT Id FROM Account]);
Here our map contains the key-value pair Id:Account. Each ID (key) unlocks the Account object (value) behind it. For instance, we can access the object by calling the value using the get(key) function. From there, we can make updates to fields. With regard to the query above, acctMap will hold a map of all IDs (keys) related 1:1 with their Accounts (values).
However, if we simply wanted to grab a set of all of those IDs (to use as a bind variable in a SOQL query, let’s say), we could add a second line of code using keySet() like this:
Map<Id,Account> acctMap = new Map<Id,Account>([SELECT Id FROM Account]);
Set<Id> acctIds = acctMap.keySet();
Or we can combine the two into one single line of code with the same result:
Set<Id> acctIds = new Map<Id,Account>([SELECT Id FROM Account]).keySet();
But let’s say, instead of the ID field, we wanted to create a set of Account names. Could we do that? Why, yes we can. With a little keySet() magic, we can return a set that goes beyond the standard ID field.
But for that, we’ll need to use an aggregate SOQL query that involves (1) grouping by the Name field, and (2) using the Id field alias on the grouped field. Here’s what I mean:
Set<String> acctNames = new Map<String,sObject>([SELECT Name Id
FROM Account
GROUP BY Name]).keySet();
Let’s break it down line by line. In the first line (where most of the magic happens), we start by assigning our variable to a set. Notice that the variable type for our Set is String: Set<String>. This is because the Account name is a string (text).
On the right side of the operator, we have new Map<String,sObject>. Here we’re setting up the map. We need a map because we’re going to use keySet() on it to pull out the keys into a set.
First, notice that instead of Id, we’re using String. Again, this is because we’re trying to create a set of Account names. (This works because an object’s ID can either be cast as Id or String.) Also, notice that we’re using sObject instead of Account. Why is that? That’s because as soon as we use the GROUP BY keyword we’ve converted the query into an aggregate query. And aggregate queries return a different sObject. Specifically, it returns AggregateResult.
So, you could actually substitute AggregateResult for sObject like this: new Map<String, AggregateResult>.
Now, here’s the fun part: we use the field alias Id for the Account name. That’s important. Honestly, we can think of this as tricking the system into believing the Account name field (Name) is the Id. We’re disguising it by throwing an Id costume over the Name. Now, when the map is created, the system will grab what it thinks is the Id to create the keys. But instead, it’s the Account name. Haha. Tricky, right?
The second line is standard SOQL query syntax (FROM Account). Nothing fancy here.
The third and final line is important because we need to group the Id alias field (Name) by using the keywords GROUP BY. This turns the SOQL query into an AggregateResult as mentioned above.
Finally, on that same last line, we use the keySet() function. This will strip the keys from the map and enable us to assign the results to a set.
Now, instead of a set of IDs, we get a set of Account names. Krazy.
If you thought that was great, there’s more! Where we can get the most bang for our buck using this trick is by creating a set of parent or grandparent Ids to be used as a bind variable.
Originally, for my use case, I was initializing a list of OpportunityLineItems using a SOQL query and it looked something like this:
List<OpportunityLineItem> oppLineItems = [SELECT Id
FROM OpportunityLineItem
WHERE Product2.Type = 'xyz'];
But my ultimate aim was to get at the underlying contacts that were associated with the Accounts that were associated with the related opportunities via the OpportunityLineItem (for a very specific product type) — phew!
If that image makes you glaze over, stick with me.
In the end, the only thing I wanted was a list of contacts (on the far right of the image). In an ideal world, I could simply query the contact and use a cross-filter to handle the rest of that stuff to its left, like this:
List<Contact> cons = [SELECT Id, Name
FROM Contact
WHERE AccountId IN (SELECT Opportunity.AccountId FROM OpportunityLineItem WHERE Product2.Type = 'xyz')];
But that’s only going to result in the following execution error: The inner select field ‘Opportunity.AccountId’ cannot have more than one level of relationships. This means you can’t step up more than one relationship level when using a subquery (or inner query) as a cross-filter.
So, the only way to get those contacts was to get a set of Account IDs and use that set as a bind variable like this:
List<Contact> cons = [SELECT Id, Name
FROM Contact
WHERE AccountId IN :accountIds];
And to get that set of Account IDs (accountIds) I could either loop over a map and add each separate ID into a set, or (as I described above) I could do this fancy move involving a map, a SOQL aggregate query, and the keySet() function:
Set<Id> accountIds = new Map<Id, sObject>([SELECT Opportunity.AccountId Id
FROM OpportunityLineItem
WHERE Product2.Type = 'xyz'
GROUP BY Opportunity.AccountId].keySet();
List<Contact> associatedContacts = [SELECT Name
FROM Contact
WHERE AccountId IN :accountIds];
And now I had a set of Account IDs to use as a bind variable. Simple. Elegant. But a bit confusing to explain. So, for that, thanks for sticking with me!
Note: It’s important to pointout that the values for the target field (eg, Id, Email, Name) cannot be null. If any are null, you’ll get an error when using the aggregate query/keySet trick outlined above. Therefore, when choosing a field that could be null, such as Email, you must includea WHERE clause that filters out any null values as seen in the first example above (ie, WHERE Email != null). Obviously, Id fields will never be null, the same for required fields, such as Contact LastName.
Finally, the only time this trick has not worked (to date) is when trying to create a set of User emails. For whatever reason it throws the follwing cryptic error: UNKNOWN_EXCEPTION: invalid parameter value. I can create all kinds of other User field sets, including UserName, but not Email. If I ever figure out why, I’ll come back and let you know. (And if you know, leave a comment below!)