10

Assume I have a list of Objectx__c.

Each instance of Objectx__c has a TEXT field SerializedData__c that contains two JSON values detailName and step.

The requirement Using the detailName and step as a composite key from any Objectx__c instance, one should be able to derive the logic reference for that instance from the Rule__c object (ie assign the rule id to the Logic__c field ).

Example Code I have set out the first pass at this code below but it's not bulkified.

for (Objectx__c  objectx : objectxs ) {
    Map<String,Object> deSerializedData = (Map<String,Object>)Json.deserializeUntyped(objectx.SerializedData__c); 

    Rule__c rule = [
        SELECT Id 
        FROM Rule__c
        WHERE DetailName__c  = :(String)deSerializedData.get('detailName')
        AND Step__c = :(Integer)deSerializedData.get('step')
    ];

    objectx.Logic__c = rule.Id;
}

I have tried a map using a key that combines detailName and step ( detailName + '###' + step ) but the code broke the 50 line barrier.

I'm sure there is a cleaner approach - all ideas appreciated.

9

This could be a tough one! Might depend on how many rows you need at once in a bulk operation?

So the original WHERE clause looks like:

(DetailName__c = 'abcde' AND Step__c = 12345)

and at first I'm thinking a bulkified WHERE clause could look like:

(DetailName__c = 'abcde' AND Step__c = 12345) OR
(DetailName__c = 'fghij' AND Step__c = 23456) OR
(DetailName__c = 'klmno' AND Step__c = 34567) ...

by building a big long string for dynamic SOQL while iterating over the map values:

List<String> clauses = new List<String>();
for (Objectx__c  objectx : objectxs ) {
    Map<String,Object> deSerializedData = (Map<String,Object>)Json.deserializeUntyped(objectx.SerializedData__c); 
    String detailName = (String)deSerializedData.get('detailName');
    Integer step = (Integer)deSerializedData.get('step');

    String clause = ''
        + '('
        + SObjectType.Rule__c.Fields.DetailName__c.Name
        + ' = '
        + '\'' + escapeSingleQuotes(detailName) + '\''
        + ' AND '
        + SObjectType.Rule__c.Fields.Step__c.Name
        + ' = '
        + step
        + ')'
    ;

    //should look like (DetailName__c = 'someName' AND Step__c = 65536)
    clauses.add(clause);
}

String where = String.join(clauses, ' OR ');
List<Rule__c> rules = Database.query(''
    + 'SELECT Id'
    + 'FROM ' + SObjectType.Rule__c.Name
    + 'WHERE ' + where
);

But now one is reminded about the 20,000 character limit for SOQL queries. Recently @sfdcfox observed that bind variables do not blow out the query length. So I wonder if it's possible to leverage a formula field, so that a Set<String> bind variable can be dropped into dynamic SOQL?

Maybe create a Text formula on Rule__c called DetailNameAndStep__c with formula:

DetailName__c + '#' + Step__c

which would evaluate to eg abcde#12345 on a typical Rule. Then, at the expense of another field on the object, we have less code, and less risk of blowing the character length limit:

Set<String> composites = new Set<String>();
for (Objectx__c  objectx : objectxs ) {
    Map<String,Object> deSerializedData = (Map<String,Object>)Json.deserializeUntyped(objectx.SerializedData__c); 
    String composite = (String)deSerializedData.get('detailName') + '#' + (Integer)deSerializedData.get('step');
    composites.add(composite);
}

List<Rule__c> rules = [
    SELECT Id
    FROM Rule__c
    WHERE DetailNameAndStep__c IN :composites
];

Maybe someone can comment on the indexing / table search implications of taking this approach...

  • Both great ideas and both surface the intent of the logic far better than my approach using SOQL and a map. I think I'll go for the formula approach to avoid blowing the SOQL string limit – max Feb 2 '14 at 7:27
3

Formula field (as in @user320's answer) means full table scan, worth considering if there's reasonable amount of "rules". If there are lots of them - offload the formulas result to helper text field that could be indexed (ideally marked as unique!) and have a workflow rule that populates it whenever new rule is created or one of items in the composite key changes.


I guess I'd try to make a WHERE DetailName__c IN :setOfDetails AND Step__c IN :setOfSteps.

  1. It's a pretty simple query, easy to understand if you have to maintain it, easy to expand.
  2. It will mean you're potentially fetching bit too much than you should (combination of {'a', 'b', 'c'} and {'1', '2', '3'} means up to 9 rows could be fetched).
  3. You then build a dictionary Map<String, Rule__c> only out of these results as opposed all Rules (which I imagine is what made you hit the 50K query rows limit... Which limit do you mean by "50 line barrier" by the way? 50K query rows? The script statements limit was replaced by CPU time...).

Worst case scenario - trigger that runs on 200 specially crafted records that end up as 200 unique Details and Steps. 200 * 200 = 40K, still some wiggle room (and that's assuming there indeed exist rules that fulfill all these combinations).

Real life scenarios will probably be much better - and you could always help this code a bit by inserting the data sorted for example (as stupid as "sort by serialized JSON field" sounds like)...

  • "broke the 50 line barrier" - relatively recently I became rather opinionated about the size of a "chunk" of code. I had 20-30 lines as the max. The "50 line barrier" implies something unnecessarily excessive IMHO – max May 20 '14 at 13:09
  • Ah, the good old "it should fit on one screen, otherwise it's trickier to follow the flow". I get it now. Re: your answer ("wall of code" part) - why do you have an OR in the WHERE clause? – eyescream May 20 '14 at 13:42
  • 1
    nice catch @eyescream - mybad. Changing OR to AND pretty much aligns with your three steps above. – max May 20 '14 at 15:17
  • 1
    Formulas can be indexed, but you have to ask, there's no external id equivelent to force indexing. – ca_peterson May 21 '14 at 3:15
  • @ca_peterson awesome, good to know! Still - this one probably would benefit from "standard workaround" to mark the helper field as unique too (unless support would be able to mark the formula as unique... I'm not an Oracle expert but I've never heard about unique column in materialized view or whatever it would boild down to...) – eyescream May 21 '14 at 4:31
3
+100

If SOQL behaved like mySql , the following approach would be simpler:

Assume:

SELECT Id, DetailName__c, Step__c FROM Rule__c;

+----+----------------+---------+
| Id | DetailName__c  | Step__c |
+----+----------------+---------+
|  1 | DetailXXX      | 1       |
|  2 | DetailXXX      | 2       |
|  3 | DetailXXX      | 3       |
|  4 | DetailZZZ      | 1       |
|  5 | DetailZZZ      | 2       |
|  6 | DetailZZZ      | 3       |
|  7 | DetailZZZ      | 4       |
|  8 | DetailZZZ      | 5       |
+----+----------------+---------+

Approach:

Build a set of composite keys Set<String>{ 'DetailXXX#1', 'DetailZZZ#3', ....}extracted from the serialized data

SELECT
    Id,
    DetailName__c,
    Step__c,
    CONCAT(DetailName__c, '#', Step__c) Concat
FROM Rule__c
GROUP BY Id
HAVING Concat IN ('DetailXXX#1', 'DetailZZZ#3')

Perform the query using CONCAT and HAVING clause to pull back the correct Rule__c Ids

+----+----------------+----------+------------------+
| Id | DetailName__c  | Step__c  | Concat           |
+----+----------------+----------+------------------+
|  1 | DetailXXX      | 1        | DetailXXX#1      |
|  6 | DetailZZZ      | 3        | DetailZZZ#3      |
+----+----------------+-------- -+------------------+

More details on the SOQL HAVING clause here http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_having.htm

Alternatively, To bulkify, I ended up with a wall of code below that works but is not pretty. I'm sure there is a cleaner approach.

// 1. A - Pull the serialized data from objectx's
// 1. B - Pull the key values : detailName and Step from serialized data
// 1. C - Create  composite key from 1B
// 1. D - create map of composite key to objectx / serialized data
// 1. E - fill  two sets with composite key values
String delim = '###';
Map<String,Map<String,Object>> keyToDetail = new Map<String,Map<String,Object>> ();
Set<String> detailNames = new Set<String>();
Set<Integer> steps = new Set<Integer>();
for (Objectx__c  objectx : objectxs ) {
    Map<String,Object> deSerializedData = (Map<String,Object>)Json.deserializeUntyped(objectx.SerializedData__c); 
    String detailName = (String)deSerializedData.get('detailName');
    Integer step = (Integer)deSerializedData.get('step') ;
    deSerializedData.put('step', step);
    String key = detailName + delim + step; 
    detailNames.add(detailName);
    steps.add(Step);
    Map<String,Object> keyToAttribute = new  Map<String,Object>{
        'deSerializedData' =>  deSerializedData,
        'objectx' => objectx
    };
    keyToDetail.put(key, keyToAttribute);
 }

// 2. A - pull  rules that have either a detailName OR a step 
//        in the sets built in 1E  
Map<String, Rule__c> keyTorule = new Map<String, Rule__c> ();
List<Rule__c> rules = [
    SELECT Id,  detailName__c, Step__c 
    FROM Rule__c
    WHERE detailName__c IN :detailNames 
    OR Step__c IN :steps
];
// 2. B - Map composite key (detailName and Step)  to rules 
for (Rule__c rule : rules) {
    String key = rule.detailName__c + delim + rule.Step__c; 
    keyTorule.put(key , rule);
}

// 3. A. - clear the objectx collection (will be rebuilt)
// 3. B. - Loop over the map from 1 
// 3  C. - using the composite key for each loop,
//         pull the rule from 2. and the attributes from 1.
// 3. D. - Using 3C , add objectx, only this time enriched wth 
//         the relevant rule Id
objectxs.clear();
for (String key :  keyToDetail.keySet()) {
    Rule__c rule = (Rule__c) keyTorule.get(key);   
    Map<String,Object> keyToAttribute = (Map<String,Object>) keyToDetail.get(key);
    Objectx__c objectx =  (Objectx__c) keyToAttribute.get('objectx');
    Map<String,Object> deSerializedData =   (Map<String,Object>) keyToAttribute.get('deSerializedData');

    objectx.SerializedData__c = Json.serializePretty(deSerializedData);
    objectx.Rule__c = rule.Id;
    objectxs.add(objectx); 
}
  • 1
    To avoid your wall of code, could you not maintain the composite key on the object itself, say with a Trigger? You could create the field as an external id field meaning you would avoid table scans a la un-indexed formula and also upsert by it. I'm guessing your Rules wouldn't change that often so the overhead of the Trigger would be minimal. Just a thought. – Phil Hawthorn May 22 '14 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.