Using multiple variable with same name in dynamic SOQL

Question

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>{'[email protected]','[email protected]','[email protected]'});

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 ('[email protected]','[email protected]','[email protected]')

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

Answer 1

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.