I am wondering what would be the most efficient way to query some objects by the pairs of values, for example:

public class pair{
    integer x;
    boolean y;
List<pair> pairs = new List<pair>();
pairs.add(new pair(2, true));
pairs.add(new pair(3, false));
pairs.add(new pair(4, true));

Now I would like to find all objects of type T where some field A = x and B = y for each pair from list. Is there some efficient way to do this, avoiding limits?


My first thougth was to create a dynamic query like this:

Select Id, Name 
From Object__c
Where ((field1__c = '1' AND field2__c = '2') OR (field1__c = '3' AND field2__c = '4') OR ... )

The code needed for that can look like the following:

String query = 'Select Id From Object__c Where (';
Integer counter = 1;
for(pair p : pairs){
    query += '(field1__c = \'' + p.x + '\' AND field2__c = ' + p.y + ') ';
    if(counter < pairs.size())
        query+= ' OR ';
query += ')';

The other way to do such queries (and I think it is an eleganter way) is to create a new formula field that concatenates two fields into the one sting and then just bind a list variable in the dynamic SOQL query.

Formula field generates a sing like this:

field1__c + '/' + field2__c

The apex code for the query generation:

list<string> str = new list<string>();
for(pair p : pairs){
    str.add(p.x + '/' + String.valueOf(p.y));
String query = 'Select Id From Object__c Where MyFormula__c = : str ';
List<Object__c> obj = Database.query(query);
  • 1
    I actually prefer the former solution, but I guess that is likely a matter of opinion (and my laziness in not wanting to make special fields for stuff like this). Don't forget to include String.escapeSingleQuotes around those inputs on the dynamic query. SOQL injection isn't nearly as bad as SQL injection (little bobby drop tables), but it can definitely still hurt. – drakored Dec 19 '14 at 9:42
  • @drakored I just give an idea. The programmer himself have to create a production code. – Sergej Utko Dec 19 '14 at 9:46
  • agreed, but this code will live here forever and someone relatively new could pick up the code sample with a critical security error. At any rate, our comments will hopefully take care of that anyway :) – drakored Dec 19 '14 at 9:56
  • 1
    N.B. the concatenated composite field will not be indexed so table scan will ensue – cropredy Oct 29 '18 at 23:43

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.