Suggestion to avoid for loop inside a for loop

Question

I'm starting out new in APEX. I created below method to call inside a trigger. I know my code below is not efficient calling loop inside a loop to avoid SOQL. I looked online for a day before posting this to look. Any suggestions? I'm not able to figure our how use Maps or Sets to achieve this.

public Static List<Quote> getFactors(List<Quote> lstq){
    List<Margin_Factor__c> lstMarginFactors = [
        SELECT
            Channel__c, Cost__c, Deal_type__c, Margin_type__c, Margin_Unit__c,
            Product_type__c, Range_High__c, Range_low__c, Swing_Type__c
        FROM Margin_Factor__c
    ]; 

    for (Quote q: lstq){
        for (Margin_Factor__c m: lstMarginFactors){
            // Acquisition Cost in $/MWh
            if(m.Channel__c==q.Sales_Channel__c && m.Deal_type__c==q.Enrollment_Type__c && m.Margin_type__c=='COA' && m.Range_low__c <= q.Term_MWh__c && m.Range_high__c > q.Term_MWh__c){
                q.COA_Factor_MWh__c = m.Cost__c;
            }
            // Cost to Serve in $/Month/ESID
            if(m.Channel__c==q.Sales_Channel__c && m.Deal_type__c==q.Enrollment_Type__c && m.Margin_type__c=='CTS' && m.Range_low__c <= q.Term_MWh__c && m.Range_high__c > q.Term_MWh__c){
                q.CTS_Factor_per_Month__c = m.Cost__c;
            }
            // Extra margin in $/MWh
            if(m.Channel__c==q.Sales_Channel__c && m.Deal_type__c==q.Enrollment_Type__c && m.Margin_type__c=='Extra' && m.Range_low__c <= q.Term_MWh__c && m.Range_high__c > q.Term_MWh__c){
                q.Extra_Margin_MWh__c = m.Cost__c;
            }
        }
    }
    return lstq;       
}

There are about 4 fields in common between Quote and Margin_factor__c custom object. And additional 2 fields that define range for a field in Quote. I'm not sure how I can map.

Margin_Factor__c has factors maintained based on common fields as described below:

Common fields between quote and Margin_Factor__c are Channel, Deal_type and Term_MWh__c on quote that fall between Range_high and Range_low in MarginFactor. I have to derive corresponding factors from MarginFactor based on the values from Quote.

Answer 1

You can use a custom type as your map key here. You'll have to abuse the functionality a bit to make ranges work, but below is a working example.

public class MarginFactorKey
{
    final Decimal low, high;
    final String channel, dealType;
    public MarginFactorKey(String channel, String dealType, Decimal low, Decimal high)
    {
        this.channel = channel;
        this.dealType = dealType;
        this.low = low;
        this.high = high;
    }
    public Integer hashCode()
    {
        return 31 * channel.hashCode() ^ dealType.hashCode();
        // don't hash the range values or you will only get an exact match
        // you'll deal with the range matching below
    }
    public Boolean equals(Object instance)
    {
        MarginFactorKey that = (MarginFactorKey)instance;
        return this.channel == that.channel && this.dealType == that.dealType &&
            that.low <= this.low && that.high >= this.high;
        // first line of Boolean evaluation gives you exact match on String values
        // second line of Boolean evaluation gives you range match on Decimal values 
    }
}

If you're having trouble wrapping your head around this key type, try it with just the Decimal portion or just the String portion. Below is a test method which runs through some input/output combos. It too might help you grok what's going on with this key type.

Map<MarginFactorKey, Integer> demo = new Map<MarginFactorKey, Integer>
{
    new MarginFactorKey('A', 'B', 1, 10) => 5,
    new MarginFactorKey('C', 'D', 1, 10) => 7,
    new MarginFactorKey('M', 'N', 10, 100) => 50,
    new MarginFactorKey('O', 'P', 10, 100) => 75,
    new MarginFactorKey('W', 'X', 100, 1000) => 500,
    new MarginFactorKey('Y', 'Z', 100, 1000) => 750
};

system.assertEquals(5, demo.get(new MarginFactorKey('A', 'B', 2, 2)));
system.assertEquals(7, demo.get(new MarginFactorKey('C', 'D', 3, 9)));
system.assertEquals(50, demo.get(new MarginFactorKey('M', 'N', 12, 42)));
system.assertEquals(75, demo.get(new MarginFactorKey('O', 'P', 25, 75)));
system.assertEquals(500, demo.get(new MarginFactorKey('W', 'X', 200, 250)));
system.assertEquals(750, demo.get(new MarginFactorKey('Y', 'Z', 500, 600)));

system.assertEquals(null, demo.get(new MarginFactorKey('W', 'X', 2, 200)));
system.assertEquals(null, demo.get(new MarginFactorKey('C', 'D', 21, 25)));
system.assertEquals(null, demo.get(new MarginFactorKey('O', 'P', 25, 200)));

I recommend you use the factory pattern to simplify creating these keys, though it is not necessary:

public class MarginFactorKey
{
    // instance methods defined above

    public static MarginFactorKey factory(Quote record)
    {
        return new MarginFactorKey(
            record.Sales_Channel__c, record.Enrollment_Type__c,
            record.Term_MWh__c, record.Term_MWh__c
        );
    }
    public static MarginFactorKey factory(Margin_Factor__c record)
    {
        return new MarginFactorKey(
            record.Channel__c, record.Deal_Type__c,
            record.Range_Low__c, record.Range_high__c
        );
    }
}

You will still need separate maps for each Margin_Type__c, but you will not have to loop through each Margin_Factor__c to find the correct records, if you put all the above together.

public static List<Quote> setFactors(List<Quote> records)
{
    Map<MapKey, Margin_Factor__c> coaFactors = new Map<MapKey, Margin_Factor__c>();
    Map<MapKey, Margin_Factor__c> ctsFactors = new Map<MapKey, Margin_Factor__c>();
    Map<MapKey, Margin_Factor__c> extraFactors = new Map<MapKey, Margin_Factor__c>();
    for (Margin_Factor__c factor : [/*factor query*/])
    {
        if (factor.Margin_Type__c == 'COA')
            coaFactors.put(MarginFactorKey.factory(factor), factor);
        if (factor.Margin_Type__c == 'CTS')
            ctsFactors.put(MarginFactorKey.factory(factor), factor);
        if (factor.Margin_Type__c == 'Extra')
            extraFactors.put(MarginFactorKey.factory(factor), factor);
    }

    // now you can avoid nested loops!

    for (Quote record : records)
    {
        MarginFactorKey key = MarginFactorKey.factory(record);
        if (coaFactors.containsKey(key))
            record.COA_Factor_MWh__c = coaFactors.get(key).Cost__c;
        if (ctsFactors.containsKey(key))
            record.CTS_Factor_per_Month__c = ctsFactors.get(key).Cost__c;
        if (extraFactors.containsKey(key))
            record.Extra_Margin_MWh__c = extraFactors.get(key).Cost__c;
    }
}