2

My question is continuation from here

This is the sample SOQL query which I'm trying to build dynamically and I have dozens of fields, it has AND OR

My question is: I'm trying to figure out how to combine both AND/OR condition in the below code so based on the sample SOQL.

SELECT Id FROM Object__c WHERE ( name  ='bob') AND ( address__c = 'my street') OR (State__c = 'CA') OR ( country__c 'USA')

here is what I assembled so far:

List<String> field_names = new List<String>
                        {'empNum1__c', 'empCode2__c', 
                         'empCode3__c','empCode4__c', 'empCode5__c'
                         'empCode6__c', 'empCode7__c', 'empCode8__c'  
                        };

list<Criteria> all_criterias = new list<Criteria>();

for(String field_name : field_names) {
   all_criterias.add(new PaymentCriteriaHelper(field_name, 'hardcodefornow'));
} 

String where_cond = generateWhereQuery(all_criterias);

String soql = 'SELECT Id FROM Nice_Object__c WHERE ' + where_cond;


public String generateWhereQuery(List<PaymentCriteriaHelper> criterias) {
    List<String> and_conditions = new List<String>();
    for(PaymentCriteriaHelper single_criteria : criterias) {
        if (single_criteria.IsFieldValueGiven()) {
            and_conditions.add(single_criteria.getSOQLCriteria());
        }
    }
    return String.join(and_conditions, ' AND ');
}

public class Criteria {
   public String FieldName;
   public String Operator;
   public String FieldValue;
   public Criteria(String f_name, String f_value) {
       FieldName = f_name;
       FieldValue = f_value;
       Operator = '=';
   }
   public String getSOQLCriteria() {
       return fieldName + ' '  + Operator + ' \'' + FieldValue + '\'';
   }

   public boolean IsReady() {
       return FieldValue != null && FieldValue != '';
   }
}
  • First, your example appears to be not very correct (e.g. ` Latitude__c >= 3 AND Longitude__c>= -8 AND Latitude__c <= 4 AND Longitude__c <= 8`), second - individual fields logic is combined into branch, and then there is a logic for branches (both could have ANDs and ORs, as well as subbranches). Do you have in mind any particular query logic you want to implement or you are trying to create a universal class? – o-lexi Jul 20 '17 at 23:25
  • @Oleksiy, it was just an example as I said in my question to get an idea how I'm using AND/OR conditions by no means that SOQL I posted is working or logical correct. – Nick Kahn Jul 20 '17 at 23:26
  • Kinda hard to help when you don't provide the logic of what you are trying to do and say the code you provided is not valid. Can you provide some rules you are trying to implement to determine the grouping of the fields in the filter? – Eric Jul 20 '17 at 23:55
  • I'm sorry for the confusion and I have updated my question with simple sample SOQL, I'm trying to find out how to write a dynamic query for sample soql and hope this clear things up. – Nick Kahn Jul 20 '17 at 23:59
  • Hi Nick, have you tried out my answer? I feel it's the best way to abstract out a set of SOQL params. – Caspar Harmer Jul 22 '17 at 2:20
1

In your situation I can suggest you to introduce Complex conditions instead of simple Criterias. Here is an example, written in pseudocode:

public interface Condition {
    String getSOQLCriteria();
    Boolean isValid();//Skip all invalid conditions
}

public class SimpleCondition implements Condition {
    public String FieldName;
    public Object FieldValue;
    public String Operator;
    public Boolean IsNegate;

    public SimpleCondition(String f_name, String f_value) {
        Operator = '=';
        FieldName = f_name;
        FieldValue = f_value;
        IsNegate = false;// Why not
    }

    public Boolean isValid() {
        return f_value != null ;
    }
    public String getSOQLCriteria() {
        return  (IsNegate?'NOT(':'')
                + fieldName + ' '  
                + Operator 
                + ' \'' + FieldValue + '\''
                (IsNegate?')':'');
    }
}

public class ComplexCondition implements Condition {
    public List<Condition> ChildConditions;
    public String ConditionLogic;//AND or OR

    public ComplexCondition(String condition_logic) {
        ChildConditions = new List<Condition>();
        ConditionLogic = condition_logic;
    }
    public void addCondition(Condition c) {
        ChildConditions.add(c);
    }

    public Boolean isValid() {
        Boolean valid_child_exists = false;
        for(Condition c : ChildConditions) {
            if (c.isValid()) {
                valid_child_exists = true;
                break;
            }
        }
        return valid_child_exists;
    }
    public String getSOQLCriteria() {
        List<String> child_conditions_soql = new List<String>();
        for(Condition c : ChildConditions) {
            if (c.isValid()) {
                child_conditions_soql.add('(' + c.getSOQLCriteria() + ')');
            }
        }
        return String.join(child_conditions_soql, ConditionLogic);
    }
}

In that scenario, you can write your logic to build dynamic query as given below:

List<List<String>> structured_fields = new List<List<String>> 
    { new List<String>{'field_1__c','field_2__c'},
      new List<String>{'field_3__c'}
    };

ComplexCondition main_condition = new ComplexCondition('OR');
for(List<String> child_fields  : structured_fields) {
    ComplexCondition and_conditions = new ComplexCondition('AND');
    for(String field : child_fields) {
        and_conditions.addCondition(
              new SimpleCondition(field, (String)some_sobject.get(field))
        );
    }
    main_condition.addCondition(and_conditions);
}
String dynamic_parameter = main_condition.getSOQLCriteria();
String dynamic_query = 'SELECT Id FROM Something__c ' 
                     + (dynamic_parameter != ''? ' WHERE ' + dynamic_parameter : '');

Given code can be enhanced to support more operators or more complex criterias if needed.

1

Starting with this query, it's easier to rewrite in functional form.

SELECT Id FROM Object__c 
WHERE ( Name  ='bob' AND Address__c = 'my street' ) 
  OR (State__c = 'CA') 
  OR ( Country__c 'USA')

Rewrite as:

WHERE(
  OR (
    AND( Name ='bob', 
         Address__c = 'my street' ),
    OR (State__c = 'CA'),
    OR (Country__c = 'USA') 
  )
)

Now adding new conditions is easy (ish):

WHERE(
  OR (
    AND( Name ='bob', 
         Address__c = 'my street' 
    ),
    OR (State__c = 'CA', 
        AND (Zip__c = '94401,
             Salary__c = 100000)
    ),
    OR (Country__c = 'USA') 
  )
)

So you can now see that we have another class to add - a LogicFunction:

public class LogicFunction{

  public String logicOperator {get;set;} //OR or AND

  public Criteria[] critera {get;set;} //top level criteria eg name = 'bob'

  public LogicFunction[] innerFuncs {get;set;} //logic functions within this function

  public LogicFunction(String logicOperator, Criteria[] critera, LogicFunction[] innerFuncs) {
      logicOperator = logicOperator;
      critera = critera;
      innerFuncs = innerFuncs;
  }
}

Constructing the data structure looks like this (probably easier to do in JSON and deserialize)

new LogicFunction('OR', new Criteria[]{}, new LogicFunction[]{
  new LogicFunction('AND',
                     new Criteria[]{
                         new Criteria('Name','bob'),
                         new Criteria('Address__c','my street')
                     }
                     new LogicFunction[]{}),
  new LogicFunction('OR',
                     new Criteria[]{
                         new Criteria('State__c','CA')
                     }
                     new LogicFunction[]{
                         new LogicFunction('AND',
                             new Criteria[]{
                                 new Criteria('Zip__c','94401'),
                                 new Criteria('Bearded__c','True')
                             }
                             new LogicFunction[]{})
                     }),
  new LogicFunction('OR',
                     new Criteria[]{
                         new Criteria('Country__c','USA')
                     }
                     new LogicFunction[]{}),
}

I'm going to leave the unpacking as an exercise - use a recursive function to build the string.

You simply evaluate the logic operator (put between any criteria and also between any child logic functions).

Keep going till you have no more logic functions. Voila!!

0

can be try this way, but you have add your fields and values to Map and iterate over it.

Map<String,String> fileds=nwe Map<Strng,String>();
    fields.put('name','AND');
    fields.put('address__c','AND');
    fields.put('State__c','OR');
    fields.put('country__c','OR');

    Map<String,String> filedsValues=nwe Map<Strng,String>();
    filedsValues.put('name','bob');
    filedsValues.put('address__c','my street');
    filedsValues.put('State__c','CA');
    filedsValues.put('country__c','USA');

    String Query='';

    for(String key;fileds.keySet())
       if(!String.isBlank(filedsValues.get(key))) 
         Query = Query + fileds.get(key)  + ' ' + key + ' = \''+  filedsValues.get(key)  +'\' '; 

     Query='SELECT Id FROM Object__c WHERE ' + Query ;

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.