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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .