SOQL with two conditiions

Question

I am retrieving two properties from a record (object1) on update, and I want to look up another record (object2) that has both of these properties.

So, for example:

object1record.email = '[email protected]'
object1record.name = 'bill'

If I just have one object1 record I can make query:

[SELECT id FROM object2__c WHERE email = :object1record.email AND name = :object1record.name]

But what should I do if I want to bulkify my code and look up results for multiple object1 records?

At the moment I am doing this with my object1List:

Map<String, Map<String, Boolean>> emailNameMatchMap = new Map<String, Map<String, Boolean>>();
List<String> emailList = new List<String>();

//**use object1 name and email as keys in 2 dimensional map and place email into list:

for(object1__c object1: object1List){
    emailList.add(object1.email);
    if(!emailNameMatchMap.containsKey(object1.name)){
          emailNameMatchMap.put(object1.name, new Map<String, Boolean>());
      }
      emailNameMatchMap.get(object1.name).put(object1.email, true);
}

//**search for object2 which match email from list:

object__2[] object2List = [SELECT email__c, name FROM object2 WHERE email IN :emailList];
List<String> object2MatchList = new List<String>();

//** find the object 2 which have the exact email and name match using the previously created map:

for(object2__c object2: object2List){
    if(emailNameMatch.get(object2.name).get(object2.email)){
        object1MatchList.add(object2);
}
}

Is there an easier way of doing this with just a SOQL query?

Answer 1

What you're looking for, basically, is a composite key. There's a few ways to do this, but I would probably prefer a custom class for this:

public class EmailNamePair {
  public String name, email;
  public EmailNamePair(String name, String email) {
    this.name = name;
    this.email = email;
  }
  public Boolean equals(Object o) {
    EmailNamePair other = (EmailNamePair)o;
    return other.name == name && other.email == email;
  }
  public Integer hashCode() {
    return (name+'♥'+email).toLowercase().hashCode();
  }
}

---

Set<String> emails = new Set<String>, names = new Set<String>();
Map<EmailNamePair> results = new Map<EmailNamePair>();
for(Object1__c record: inputs) {
  emails.add(record.email);
  names.add(record.name);
}
for(Object2__c record: [select name, email from object2__c where name = :names and email = :emails]) {
  results.put(new EmailNamePair(record.name, record.email), record);
}
for(Object1__c record: inputs) {
  Object2__c relatedRecord = results.get(new EmailNamePair(record.name, record.email));
  if(relatedRecord != null) {
    // ... you have a match
  }
}

Note, however, that the overall implementation isn't going to be shorter than your code, but it may very well be faster/bulkified. As a side note, if all the fields are editable (e.g. Lead and Contact Name fields are compound fields and can not be set directly in Apex), you can just use one of the objects as the key, saving you about 13 lines of code, as I demonstrate in this answer.

Answer 2

Not necessarily an easier way but I do see 2 options for improvement:

Filter SOQL also on the names. This will reduce the number of records to process:

object2__c[] object2List = [SELECT email__c, name FROM object2__c WHERE email IN :emailList AND Name in :emailNameMatchMap.keySet];

Create a filter key field on object__2, consisting of a concatenation of Email__c and Name. e.g. Keyfield__c. This could be a formula field. Then create a list of key filters from your loop through object1__c.

  List<String> keyFieldFilterList = new List<String>();
  for(object1__c object1: object1List){
    keyFieldFilterList.add(object1.Email__c+object1.Name);
  }

Then filter on those key filters:

object2__c[] object2List = [SELECT email__c, name FROM object2__c WHERE Keyfield__c in :keyFieldFilterList];

This way you will not have to do any post-processing as all object2__c records returned will be full matches with the object1__c list.