Fun with SOQL and Keys

If you have experience with SQL, SOQL feels both familiar and a bit insubstantial, like drinking a cup of flavored decaf when you are used to freshly roasted Colombian (please note that I have the palette of a barbarian – forgive the analogy if you are a coffeephile).  Once the coffee has kicked in and the day is started, I frequently find myself either looking for unique values, or trying to validate unique records with Apex and SOQL.

SOQL does have GROUP and ORDER BY which are very helpful when searching for unique values in a collection of records.  However, it’s upsetting that SOQL has no DISTINCT keyword.   Typically if you need distinct values you create a SET:

// create Set of Contact.Title to get distinct values

Set <String> setTitles = new Set <String> ();
for (Contact con : [select Title from Contact where Title != null order by Title]) {
setTitles.add(con.Title);
}

A pattern that I use frequently for enforcing unique records is an alternate primary key.  You can create alternate primary keys using formula fields, then use these in your SOQL.

For example, if you’re trying to find duplicate Contact records you can use formula fields to create an key, then search for that key. Lets use a combination of First Name, Last Name, and email domain. So we first add a custom formula field on Contact:

Field name: Contact.Alternate_Primary_Key

Field type: formula (text)

Formula definition: LOWER(FirstName) + '::'+LOWER(LastName)+'::'+SUBSTITUTE(Email, LEFT(LOWER(Email), FIND("@", Email)), NULL)

Now searching for and removing duplicates from a list of new Contacts is simple:

 1  // do not insert duplicate Contacts
 2  
 3  // first, check for any existing records
 4  Set  setDupeKeys = new Set  ();
 5  for (Contact con : lstNewContacts) {
 6  String duplicateKey = con.FirstName + '::' + con.LastName + '::' + con.Email.substring();
 7    setDupeKeys.add(duplicateKey.toLowerCase());
 8  }
 9
10  // next find any existing Contacts with the duplicate key
11  Set  setExistingDupeKeys = new Set  ();
12  for (Contact con : [select Alternate_Primary_Key__c from Contact where Alternate_Primary_Key__c
 in :setDupeKeys]) {
13    setExistingDupeKeys.add(con.Alternate_Primary_Key__c);
14  }
15 
16  // now remove any Contacts from our insert list that have a key we found in existing records
17  List  lstDedupedContacts = new List  ();
18  for (Contact con : lstNewContacts) {
19    String duplicateKey = con.FirstName + '::' + con.LastName + '::' + con.Email.substring();
20    if (!setExistingDupeKeys.contains(duplicateKey.toLowerCase())) {
21      lstDedupedContacts.add(con);
22    }
23  }
24
25  // finally insert the deduped list
26  insert lstDedupedContacts;

If we want to enforce this to prevent duplicate Contact records we can use an additional unique text field on Contact like this:

Field name: Contact.Unique_Key

Field type: text (Be sure that Unique is checked – note that we are inserting lowercase values, so case doesn’t matter)

This field can be updated by trigger or workflow with the value from Contact.Alternate_Primary_Key.

I use variations on these patterns frequently.  They are useful for filtering results.  They are also helpful when ensuring uniqueness for records or groups of records.