Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Colin LoretzColin Loretz 

How to write 'in' statement in dynamic apex

Code:
partnerResults = [Select Id, Name, 
(Select Id, Name, Opportunity__c, Opportunity__r.StageName from Partners_Sent__r 
where Opportunity__c != null AND Opportunity__r.CloseDate = THIS_YEAR) 
from CVB_Partner__c where Id in :partnerList];

 
I'm having an issue writing this dynamically. I would like to do so and substitute THIS_YEAR with a date picklist. However, when I write it as a query String, I get an error from the colon (:) in front of partnerList.

How might I do an 'IN' statement within a dynamic apex query?

Thanks.

Colin
mikefmikef
You can use this method, please update the code to fit your needs.

Code:
public String setWhereClause(Set<Id> ids){
        String queryString = ' where Id in (';
        Integer setSize = ids.size();
        List<Id> idArray = new List<Id>();
        for(Id i : ids){
            idArray.add(i);
        }
        
        for(Integer i = 0; i < setSize; i++){
            if((setSize - i) == 1){
                queryString += '\'' + idArray[i] + '\'';
            }else{
                queryString += '\'' + idArray[i] + '\',';
            }
        }
        queryString += ')';
        return queryString;
    }

 It takes a set of Ids and coverts them into a List, then goes through the list adding the ids to a string.
The out put for a set containing two Ids is:
'where Id in (\'sobjectId0\',\'sobjectId1\')'

That should work.

If you variable partnerList is already a List of Ids or an Array then take out the Set to List for loop and pass in the List.
Colin LoretzColin Loretz
Thanks Mike, I'll give that a shot tonight.
Colin LoretzColin Loretz
The code looks like it should work but the class refuses to save:



String fullQuery = 'Select Id, Name, (Select Id, Name, Opportunity__c, Opportunity__r.Name, Opportunity__r.Total_RN__c, Opportunity__r.Arrival_Date__c, Opportunity__r.Departure_Date__c, Opportunity__r.Budget_Rate_Range__c, Opportunity__r.Industry__c, Opportunity__r.StageName from Partners_Sent__r where Opportunity__c != null AND Opportunity__r.CloseDate = THIS_YEAR) from CVB_Partner__c where Id in (';

Integer setSize = partnerList.size() ;

for(Integer i = 0; i < setSize; i++){
if((setSize - i) == 1){
fullQuery += '\'' + partnerList[i] + '\'';
}else{
fullQuery += '\'' + partnerList[i] + '\',';
}
}
fullQuery += ')';

partnerResults = Database.query(fullQuery);



Save error: Unable to perform save on all files: An unexpected error has occurred. Please try again, or check the log file for details.


Would the length of the list cause this?
mikefmikef
The error has nothing to do with the query, or at least that is what it looks like.

One question why are you using the string query method if you are hand writing all the fields you want to pull back.

I ask because you are doing an extra step and it's not needed. You can just write your query like this.

Code:
List<CVB_Partner> partnerList = [Select Id, Name, 
                                  (Select Id,  
                                          Name, 
                                          Opportunity__c, 
                                          Opportunity__r.Name, 
                                          Opportunity__r.Total_RN__c, 
                                          Opportunity__r.Arrival_Date__c, 
                                          Opportunity__r.Departure_Date__c, 
                                          Opportunity__r.Budget_Rate_Range__c, 
                                          Opportunity__r.Industry__c, 
                                          Opportunity__r.StageName 
                                   from Partners_Sent__r 
                                   where Opportunity__c != null 
                                   AND Opportunity__r.CloseDate = THIS_YEAR) 
                                from CVB_Partner__c 
                                where Id in : idSet];

 Database.query(string); is normally used for Dynamic Soql, away to get back all the fields from an object even if you don't know about them.

I am not sure about the save error, try saving the file through the UI all commented out. Then un-comment parts of your code to see what part is the issue.



Message Edited by mikef on 11-18-2008 08:08 AM
Colin LoretzColin Loretz
Mike,

I'm using dynamic soql to search for records using date and alpha criteria, I just parsed down the extraneous information for this example.

I believe I have found the source of the save error, which actually does have to do with the query. If the query string is not built right, for instance, using the wrong date format, it won't allow me to save it.

Thanks,
Colin