4

I have one object XYZ__c having two record types say A and B. I need to create junction Object JUNC__c(Fields: A__c(Lookup-XYZ__z),B__c(Lookup-XYZ__c)) record if XYZ__c record fields are same. I implemented as below,

List<XYZ__c> lstA =  [select id,f1__c,f2__c from XYZ__c where recordType.name ='A'];    
List<XYZ__c> lstB =  [select id,f1__c,f2__c from XYZ__c where recordType.name ='B'];    
List<JUNC__c> lstToBeInserted = new List<JUNC__c>();
    for(XYZ__c A:lstA){
         for(XYZ__c B: lstB){
            if(A.f1==B.f1 && A.f2==B.f2){
               lstToBeInserted.add(new JUNC__c(A__c=A.id,B__c=B.id));
            }
         }
    }
    if(lstToBeInserted.size()>0)
       insert lstToBeInserted;

It works. but i want a way to optimize it. what if i have thousands of records. it will take lot of time.Please help!

1 Answer 1

3

Here is a map-based solution. The trick is to combine all the values that you want to match into the map key:

Map<String, Id> rtas = new Map<String, Id>();
Map<String, Id> rtbs = new Map<String, Id>();
for (XYZ__c xyz : [select id,f1__c,f2__c from XYZ__c where recordType.name in ('A', 'B')]) {
    Map<String, Id> m = xyz.recordType.name == 'A' ? rtas : rtbs;
    String key = xyz.f1__c + xyz.f2__c;
    m.put(key, xyz.Id);
}

Set<String> commonKeys = new Set<String>(rtas.keySet());
commonKeys.retainAll(rtbs.keySet());

if (commonKeys.size() > 0) {
    List<JUNC__c> inserts = new List<JUNC__c>();
    for (String key : commonKeys) {
        inserts.add(new JUNC__c(
                A__c = rtas.get(key),
                B__c = rtbs.get(key)
                ));
    }
    insert inserts;
}

PS

The approach of using a custom key class (to handle the order dependent and relative fields - see the comments below) would look much the same:

Map<Key, Id> rtas = new Map<Key, Id>();
Map<Key, Id> rtbs = new Map<Key, Id>();
for (XYZ__c xyz : [
        select id, f1__c, f2__c, f3__c, f4__c
        from XYZ__c
        where recordType.name in ('A', 'B')
        ]) {
    Map<String, Id> m = xyz.recordType.name == 'A' ? rtas : rtbs;
    m.put(new Key(xyz), xyz.Id);
}

Set<Key> commonKeys = new Set<Key>(rtas.keySet());
commonKeys.retainAll(rtbs.keySet());

if (commonKeys.size() > 0) {
    List<JUNC__c> inserts = new List<JUNC__c>();
    for (Key key : commonKeys) {
        inserts.add(new JUNC__c(
                A__c = rtas.get(key),
                B__c = rtbs.get(key)
                ));
    }
    insert inserts;
}

with the tricky stuff in the Key class:

public class Key {

    private XYZ__c xyz;
    private String t;
    private Integer hashCode;

    public Key(XYZ__c xyz) {
        this.xyz = xyz;
        this.t = xyz.recordType.name;
    }

    public Boolean equals(Object o) {
        if (o instanceof Key) {
            XYZ__c a = this.xyz;
            XYZ__c b = ((Key) o).xyz;
            if (a.f1__c != b.f1__c) return false;
            if (a.f2__c != b.f2__c) return false;
            if (a.t == b.t) {
               // Same record type, need to be identical
               if (a.f3__c != b.f3__c) return false;
               if (a.f4__c != b.f4__c) return false;
               return true;
            } else if (a.t == 'A') {
               // Record type 'A' and 'B'
               if (!a.f3__c.contains(b.f3__c)) return false;
               if (!(a.f4__c <= b.f4__c)) return false;
               return true;
            } else {
               // Record type 'B' and 'A'
               if (!b.f3__c.contains(a.f3__c)) return false;
               if (!(b.f4__c <= a.f4__c)) return false;
               return true;
            }
        } else {
            return false;
        }
    }

    // Can't include order dependent or comparison fields here as that would put
    // data in different hash buckets so matches couldn't be found
    public Integer hashCode() {
        if (hashCode == null) {
            hashCode = (String.valueOf(xyz.f1__c) + String.valueOf(xyz.f2__c)).hashCode();
        }
        return hashCode;
    }
}

This would need a good set of unit tests to be sure it worked.

5
  • can u guide me if i say i have to compare an additional multiselect picklist field as A.f3__c.contains(B.f3__c)
    – snehakem
    Jul 25, 2014 at 10:41
  • @snehakem If this is another "and" then you just query that too and include it in the key.
    – Keith C
    Jul 25, 2014 at 10:55
  • Sorry for troubling you. I would have been more specific before. Actually i have multiple fields to compare.Not only to check if its equal(A.f1__c==B.f1__c) but also contains for multiselect picklist(A.f3__c.contains(B.f3__c)), less then/greater then for number(A.f4__c<=B.f4__c). Your code would have been helpful if i had to use only equals. Is there anything i can do?
    – snehakem
    Jul 25, 2014 at 11:03
  • 1
    @snehakem Probably best to ask a new question with the complete requirements in it (that can link back to this one). Map keys that are custom classes that implement equals and hashCode can be used but with the A/B order dependency and use of comparison that would be tricky enough to get right. Perhaps there is some other approach that would work better. (You should definitely only query once for XYZ__c and cache the values: your original solution will hit the query governor limit very easily.)
    – Keith C
    Jul 25, 2014 at 11:25
  • @KeithC this is a very elegant solution. Small fix though: The fourth line should read Map<String, Id> m = xyz.recordType.name == 'A' ? rtas : rtbs; Notice the additional equals sign? Could make all the difference ;-)
    – FrankZ
    Jul 25, 2014 at 13:04

You must log in to answer this question.

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