1

Good day everyone I have some problem in regards with using dynamic query with too many variables. To explain further below is some part of my code.

String QueryString = '';
List<List<String>> whereInList = new List<List<String>>();
WhereInList.add(new List<String>{'FirstName','0'});
WhereInList.add(new List<String>{'LastName','1'});
WhereInList.add(new List<String>{'Email','2'});

Map<String, Set<Object>> mapWhereField = new Map<String, Set<Object>>();
mapWhereField.put('FirstName',new Set<Object>{'Anna','Mary','John'});
mapWhereField.put('LastName',new Set<Object>{'Doe','Kendrick','Jane'});
mapWhereField.put('Email',new Set<Object>{'test@test.com','sample@sample.com','anna@gmail.com'});

QueryString += 'Select Id,FirstName,LastName,Email FROM Contact WHERE ';
for(List<String> whereObject : whereInList) {

  Set<Object> whereData = mapWhereField.get(whereObject[0]);

  Set<String> tempWhereList = new Set<String>();    
  for(Object parsedField: whereData){
    tempWhereList.add((String) parsedField);
  } 

  //System.debug('tempWhereList : ' + tempWhereList + ' size : ' + tempWhereList.size());
  QueryString += whereObject[0] + ' IN :tempWhereList AND ';


}

System.debug('The WHERE QUERY' + QueryString.substring(0, QueryString.length()-4));
return Database.query(QueryString.substring(0, QueryString.length()-4));

Based on the sample code above the System debug looks like this:

Select Id,FirstName,LastName,Email FROM Contact WHERE FirstName IN :tempWhereList AND LastName IN :tempWhereList AND Email IN :tempWhereList 

When I try to run my code it return no result(0 column).The thing that i'm confuse is that I am expecting to get some result because some of this data is already in salesforce. I've tried to query it in workbench and I've seen the records. This is how I put it in the workbench.

Select Id,FirstName,LastName,Email FROM Contact WHERE FirstName IN ('Anna', 'Mary', 'John') AND LastName IN ('Doe','Kendrick','Jane') AND Email IN ('test@test.com','sample@sample.com','anna@gmail.com')

My question is this, does my variable data is overriden because it has only one name ? What is the best solution to fix this using a similar concept of what I did? Please Help. Thank you

  • N.B. your third mapWhere uses the wrong key - should be 'Email' – cropredy Mar 4 '16 at 23:33
  • @crop1645 I am sorry for that typo error. I will update my question. – Hope Mar 4 '16 at 23:35
3

Well, I'll be buggered.

Apparently the variable that is declared within the for loop is still available outside the for loop. It works with if statements as well as general scope declarations.

I've created another question for those interested in following the issue of the scope of the variables: Variable scope - database.query() has access to variables not in scope

Going back to your question, I'd assume the value of tempWhereList is only calculated once; when the query is executed.

If constructing your query this way, I'd probably split it out into the various exact strings (assuming you're not working with huge amounts of data).

i.e. IN ('Anna', 'Mary', 'John') rather than IN :variable

You can achieve this by replacing:

QueryString += whereObject[0] + ' IN :tempWhereList AND ';

with:

QueryString += whereObject[0] + ' IN (\'' + String.join(new List<String>{tempWhereList}, '\',\'') + '\') AND';

Although I'd probably re-work the script a bit.

| improve this answer | |
  • @Nick Cook I am not sure if this is really what happen here. Because when I only have one value for my "whereInList" variable I will a get a correct data from my query. – Hope Mar 4 '16 at 23:32
  • @NickCook The question was edited-- it wasn't like that before. – sfdcfox Mar 5 '16 at 0:21
  • @sfdcfox I am really trying to figure out I am missing but when I try to put only one value "whereInList" variable. Like for example I will only add Email, the code will work . It will give a result. – Hope Mar 5 '16 at 0:26
  • @NickCook Can you please explain what do you mean by your first suggestion ? Can you please give me what I am missing in my current code ? – Hope Mar 5 '16 at 0:40
  • @Hope It shouldn't. Do the results actually match the criteria? – Nick Cook Mar 5 '16 at 0:44

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.