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.)

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

2 Answers 2

Reset to default
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 + '.'
           );
        }
    }
}
Improve this answer
1
  • 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, 2017 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');
Improve this answer
4
  • 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, 2014 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, 2014 at 23:22
  • 1
    Yeah I was thinking a to z...
    – Keith C
    Apr 10, 2014 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, 2014 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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