8

I understand from answers like this Bind variable with dotted name not working in dynamic SOQL that binding Apex variables into dynamic SOQL is more limited than into static SOQL.

I want to write some Apex that does a query with a variable number of "in" terms in the "where" clause and am thinking that something like this might be needed:

public SObject[] query(Map<SObjectField, Set<Object>> ins) {
    Iterator<Set<Object>> iter = ins.values().iterator();
    Set<Object> a = iter.hasNext() ? iter.next() : null;
    Set<Object> b = iter.hasNext() ? iter.next() : null;
    Set<Object> c = iter.hasNext() ? iter.next() : null;
    ...
    String soql = ''
            + ' select ... from ...'
            + ' where Aaa__c in :a and Bbbb__c in :b and Cccc__c in :c ...';
    return Database.query(soql);
}

But is there a better way to do this? (Preferably without just building the terms to match into the SOQL string.)

  • did you ever figure out an elegant solution to this? – Mark Pond Jun 13 '14 at 23:26
  • @MarkPond No we went for a brute-force solution - Ive added he code as an answer. – Keith C Jun 14 '14 at 9:33
5

This is the brute-force code my colleague ended up writing; not only multiple named bind fields but the fields needed to be of the right type:

public with sharing class DynamicQuery {

    private Set<String> aString = new Set<String>();
    private Set<String> bString = new Set<String>();
    private Set<String> cString = new Set<String>();
    private Set<String> dString = new Set<String>();
    private Set<String> eString = new Set<String>();

    private Set<Decimal> aDecimal = new Set<Decimal>();
    private Set<Decimal> bDecimal = new Set<Decimal>();
    private Set<Decimal> cDecimal = new Set<Decimal>();
    private Set<Decimal> dDecimal = new Set<Decimal>();
    private Set<Decimal> eDecimal = new Set<Decimal>();

    private Set<Date> aDate = new Set<Date>();
    private Set<Date> bDate = new Set<Date>();
    private Set<Date> cDate = new Set<Date>();
    private Set<Date> dDate = new Set<Date>();
    private Set<Date> eDate = new Set<Date>();

    private Set<DateTime> aDateTime = new Set<DateTime>();
    private Set<DateTime> bDateTime = new Set<DateTime>();
    private Set<DateTime> cDateTime = new Set<DateTime>();
    private Set<DateTime> dDateTime = new Set<DateTime>();
    private Set<DateTime> eDateTime = new Set<DateTime>();

    private Set<Boolean> aBoolean = new Set<Boolean>();
    private Set<Boolean> bBoolean = new Set<Boolean>();
    private Set<Boolean> cBoolean = new Set<Boolean>();
    private Set<Boolean> dBoolean = new Set<Boolean>();
    private Set<Boolean> eBoolean = new Set<Boolean>();

    private String soql;
    private String[] fields;
    private List<String> whereStrings;

    public DynamicQuery(String[] fields, String sobType, SObject[] sobs) {
        this.fields = fields;
        soql = 'select Id, ' + String.join(fields, ', ') + ' from ' + sobType + ' where ';

        whereStrings = new List<String>();

        Map<String, Set<Object>> whereSets = buildWhereSets(sobs);
        Integer size = whereSets.size();
        List<String> whereFields = new List<String>();
        whereFields.addAll(whereSets.keySet());

        for (String s : whereSets.keySet()) {
            //create list from set of field values to get 1st object
            List<Object> whereListFieldValues = new List<Object>();
            whereListFieldValues.addAll(whereSets.get(s));
            //get type of first object to add to its type set
            Object o = whereListFieldValues[0];
            if (o != null) {
                if (o instanceof String) {
                    addStringSet(s, whereSets.get(s));
                }
                else if (o instanceof Decimal) {
                    addDecimalSet(s, whereSets.get(s));
                }
                else if (o instanceof Date) {
                    addDateSet(s, whereSets.get(s));
                }
                else if (o instanceof DateTime) {
                    addDateTimeSet(s, whereSets.get(s));
                }
                else if (o instanceof Boolean) {
                    addBooleanSet(s, whereSets.get(s));
                }
            }
        }

        soql += String.join(whereStrings, ' and ');
    }

    public SObject[] query() {
        return Database.query(soql);
    }

    private Map<String, Set<Object>> buildWhereSets(SObject[] changed) {
        Map<String, Set<Object>> whereSets = new Map<String, Set<Object>>();
        for (String field : fields) {
            Set<Object> inSet = new Set<Object>();
            for (SObject record : changed) {
                try {
                    inSet.add(record.get(field));
                }
                catch (SObjectException e) {
                    throw new DuplicateException(
                            '"' + field + '"'
                            + ' is an invalid field name, please change your Extras'
                            + ' Duplicate Blocker custom settings to contain a valid field.'
                    );
                }
            }
            whereSets.put(field, inSet);
        }
        return whereSets;
    }

    private void addStringSet(String field, Set<Object> objectSet) {
        Set<String> stringSet = new Set<String>();
        for (Object o : objectSet) {
            stringSet.add((String)o);
        }

        if (aString.isEmpty()) {
            aString.addAll(stringSet);
            whereStrings.add(field + ' in :aString');
        }
        else if (bString.isEmpty()) {
            bString.addAll(stringSet);
            whereStrings.add(field + ' in :bString');
        }
        else if (cString.isEmpty()) {
            cString.addAll(stringSet);
            whereStrings.add(field + ' in :cString');
        }
        else if (dString.isEmpty()) {
            dString.addAll(stringSet);
            whereStrings.add(field + ' in :dString');
        }
        else if (eString.isEmpty()) {
            eString.addAll(stringSet);
            whereStrings.add(field + ' in :eString');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case String. '
               + 'The 6th String field is ' + field + '.'
           );
        }
    }

    private void addDecimalSet(String field, Set<Object> objectSet) {
        Set<Decimal> decimalSet = new Set<Decimal>();
        for (Object o : objectSet) {
            decimalSet.add((Decimal)o);
        }

        if (aDecimal.isEmpty()) {
            aDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :aDecimal');
        }
        else if (bDecimal.isEmpty()) {
            bDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :bDecimal');
        }
        else if (cDecimal.isEmpty()) {
            cDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :cDecimal');
        }
        else if (dDecimal.isEmpty()) {
            dDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :dDecimal');
        }
        else if (eDecimal.isEmpty()) {
            eDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :eDecimal');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Decimal. '
               + 'The 6th Decimal field is ' + field + '.'
           );
        }
    }
    private void addDateSet(String field, Set<Object> objectSet) {
        Set<Date> dateSet = new Set<Date>();
        for (Object o : objectSet) {
            dateSet.add((Date)o);
        }

        if (aDate.isEmpty()) {
            aDate.addAll(dateSet);
            whereStrings.add(field + ' in :aDate');
        }
        else if (bDate.isEmpty()) {
            bDate.addAll(dateSet);
            whereStrings.add(field + ' in :bDate');
        }
        else if (cDate.isEmpty()) {
            cDate.addAll(dateSet);
            whereStrings.add(field + ' in :cDate');
        }
        else if (dDate.isEmpty()) {
            dDate.addAll(dateSet);
            whereStrings.add(field + ' in :dDate');
        }
        else if (eDate.isEmpty()) {
            eDate.addAll(dateSet);
            whereStrings.add(field + ' in :eDate');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Date. '
               + 'The 6th Date field is ' + field + '.'
           );
        }
    }
    private void addDateTimeSet(String field, Set<Object> objectSet) {
        Set<DateTime> dateTimeSet = new Set<DateTime>();
        for (Object o : objectSet) {
            dateTimeSet.add((DateTime)o);
        }

        if (aDateTime.isEmpty()) {
            aDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :aDateTime');
        }
        else if (bDateTime.isEmpty()) {
            bDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :bDateTime');
        }
        else if (cDateTime.isEmpty()) {
            cDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :cDateTime');
        }
        else if (dDateTime.isEmpty()) {
            dDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :dDateTime');
        }
        else if (eDateTime.isEmpty()) {
            eDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :eDateTime');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case DateTime. '
               + 'The 6th DateTime field is ' + field + '.'
           );
        }
    }
    private void addBooleanSet(String field, Set<Object> objectSet) {
        Set<Boolean> booleanSet = new Set<Boolean>();
        for (Object o : objectSet) {
            booleanSet.add((Boolean)o);
        }

        if (aBoolean.isEmpty()) {
            aBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :aBoolean');
        }
        else if (bBoolean.isEmpty()) {
            bBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :bBoolean');
        }
        else if (cBoolean.isEmpty()) {
            cBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :cBoolean');
        }
        else if (dBoolean.isEmpty()) {
            dBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :dBoolean');
        }
        else if (eBoolean.isEmpty()) {
            eBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :eBoolean');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Boolean. '
               + 'The 6th Boolean field is ' + field + '.'
           );
        }
    }
}
  • I don't think you need to declare a list of each type I am using a List<Object> and that's work with the operator = – benahm Mar 22 '17 at 14:36
2

You can bind entire collections to an IN clause. For example this works, even with dynamic SOQL:

List<String> someValues = new List<String>{'hi', 'greetings', 'howdy'};
List<SObject> results = Database.query('SELECT id FROM MyObj__c WHERE Greeting__c IN :someValues');
  • Yes, but I'm looking to bind 1 or 2 or 3 or 4 etc such collections, hence the collection of sets and the (probable) need for multiple variables a, b etc not just the one someValues. – Keith C Apr 10 '14 at 23:21
  • Ohh, yeah in that case you're screwed, or you can go full on ugly hack and make var1, var2, var3, etc. – ca_peterson Apr 10 '14 at 23:22
  • 1
    Yeah I was thinking a to z... – Keith C Apr 10 '14 at 23:24
  • 1
    This limitation is because, unlike "real" languages that have dynamic string binding (e.g. Ruby, Perl), you can't call a function in a string query, only in inline queries. – sfdcfox Jun 14 '14 at 13:55

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.