How to pass set<id> dynamically to soql [duplicate]

Question

I have a dynamic Soql in which I want to pass a set.

I get an error:

System.QueryException: expecting a colon, found '{'

Anonymous window

Set<Id> idSet = new Set<Id> ();
idSet.add('xxxxx00000xxxxAAAR');
RS.GetOrder('xxxxx00000xxxxAAAR', idSet);

Class

 public class RS {
    ..
      public static LIST<Order__c> GetOrder(string idField, Set<Id> idSet) {
    ..
            String where_clause = '';
            if (idField != null && idSet == null) { where_clause = ' WHERE Id =\'' + idField + '\'';
    }


            String where_in_clause = '';
            if (idField != null && idSet != null) { where_in_clause = ' WHERE ' + idField + ' IN ' + idSet;
          }

            String soqlSo = 'SELECT' + cFieldsSo ' FROM order__c' + where_clause + where_in_clause;
            system.debug('soqlSo: ' + soqlSo);
            List<sObject> sobjSoList = Database.query(soqlSo);
    }

Debug

...FROM Staging_order__c WHERE xxxxx00000xxxxAAAR IN {a1x2o00000xxxxAAAR}

When I add a colon

where_in_clause = ' WHERE ' + idField + ' IN :' + idSet;

I get the error

System.QueryException: unexpected token: '{'

Answer 1

You can't concatenate a Set<Id> into a string. Instead, use Apex binding. Simple Apex binds (variable names only, no complex expressions) are supported in Dynamic SOQL, and allow you to avoid the complexity of formatting your Apex data as expected by the SOQL parser (as well as avoiding issues with quoting and escaping, and closing the window on SOQL injection attacks).

Here:

where_in_clause = ' WHERE ' + idField + ' IN :idSet';

Note that there's a different problem here: in one clause, you're substituting idField into your query as the name of a field, and in another, as a literal Id value. This is, at best, confusing API design that will likely lead to strange errors down the road.

I strongly encourage requiring idField to be the API name of a field (non-null, and optionally Id), and idSet always to be a non-null Set containing at least one value. You can always pass a Set of one Id.

If you want, you can implement an override of this method for simplicity:

  public static List<Order__c> GetOrder(Id recordId) {
      return GetOrder('Id', new Set<Id>{ recordId });
  }

  public static LIST<Order__c> GetOrder(string idField, Set<Id> idSet) {
     ...
  }