+ Start a Discussion
Linda 98Linda 98 

Help with if conditions in dynamic soql

String level ='Top';
String query='select id,Custompicklist__c  from Contact';

 if(Roleslist.size() >0){
        for(Integer i =0; i<roles.size();i++){
            if(i==0){
             query +=' where account.Active__c=true and custompicklistfield__c=:level and roles__c INCLUDES (\''+roleslist[0]+'\'';    
            }else{
            query += ',\''+roleslist[i]+'\'';
            }
           } 
                query += ')' limit 3;     
        }
        c=database.query(query);


THIS BRINGS CONTACT IDs of CONTACTS WHOSE LEVEL IS SET TO HIGH. BUT WHEN the field is set to a null or different value, then the query goes wrong and fetches 0 records.

 
Best Answer chosen by Linda 98
NehaDabas16NehaDabas16
Try below snippet:
String level ='Top';
String query='select id,Custompicklist__c  from Contact';

if(Roleslist.size() >0){
	Set<String> roleSet = new Set<String>(rolesList);
	for(Integer i =0; i<roles.size();i++){
		if(i==0){
		 query +=' where account.Active__c=true and roles__c INCLUDES (\''+roleslist[0]+'\'';    
		}else{
		query += ',\''+roleslist[i]+'\'';
		}
	} 
	query += ')';
	Boolean enforceLimit = false;
	if([SELECT count() FROM Contact WHERE  account.Active__c=true AND custompicklistfield__c=:level AND roles__c IN :roleSet LIMIT 1] > 0){
		query += ' AND custompicklistfield__c=:level';
		
		enforceLimit = true;
	}
	if(enforceLimit){
		query += ' LIMIT 3';
	}
}
c=database.query(query);

Please mark it as best answer if this resolves your issue.

Regards,
Neha
 

All Answers

NehaDabas16NehaDabas16
Hi Linda,

As you are fetching contact records for which custompicklistfield__c is set to 'Top' and all other conditions in where clause are bind with AND connector, you will not get any contacts if custompicklistfield__c field is set to any other value except 'Top'.

You should reconstruct your condition based on which records you want.
Linda 98Linda 98
Yes.could you please help me in constructing the query.
NehaDabas16NehaDabas16
Please state your functional requirements. Which contact records do want to fetch?
Linda 98Linda 98
I need two sets of contacts. If contact has custom picklist value set to 'top' I want top 3 ,if the picklist value is null or other than Top,then I need all the contacts My query now fetches top 3 contacts only if we have picklist value set to top. In other case it fetches zero records.
Andrew GAndrew G
Since we are doing a query, we don't know what the result is until we do the query, so therefore we can't build the query using an IF..THEN...ELSE type statement.

You could play with the ORDER BY feature , but this would rely to some degree on what values are stored in the picklist (and i think nulls have fun in the sort column).  If the picklist is something like "Top","Secondary","Tertiary" try the DESC key work to sort in reverse.  Then as you do your write, test the custompicklist value in the loop.
String level ='Top';
String query='select id,Custompicklist__c  from Contact';

 if(Roleslist.size() >0){
        for(Integer i =0; i<roles.size();i++){
            if(i==0){
             query +=' where account.Active__c=true and custompicklistfield__c=:level and roles__c INCLUDES (\''+roleslist[0]+'\'';    
            }else{
            query += ',\''+roleslist[i]+'\'';
            }
           } 
                query += 'ORDER BY custompicklistfield DESC )';     
        }
        c=database.query(query);

Other options 
1. If unable to get a Sort running over custompicklist field, create a formula field in the Contact record that is true / false = if (custompicklistfield__c ="Top",TRUE,FALSE) or perhaps a number value to sort the custompicklist values in an order that makes sense. Then query on that field and then loop the result and test the customepicklist value and write your values accordingly.

2. Do  2 x queries - one with custompicklist to Top  ("Top Results") and other to collect all ("Full results").  Push them both to Maps, and as you loop the "full result" set, test the "Top" Map results for the key and drag the values from that "Top" Map, not your "Full" Map.


Regards
Andrew
NehaDabas16NehaDabas16
Try below snippet:
String level ='Top';
String query='select id,Custompicklist__c  from Contact';

if(Roleslist.size() >0){
	Set<String> roleSet = new Set<String>(rolesList);
	for(Integer i =0; i<roles.size();i++){
		if(i==0){
		 query +=' where account.Active__c=true and roles__c INCLUDES (\''+roleslist[0]+'\'';    
		}else{
		query += ',\''+roleslist[i]+'\'';
		}
	} 
	query += ')';
	Boolean enforceLimit = false;
	if([SELECT count() FROM Contact WHERE  account.Active__c=true AND custompicklistfield__c=:level AND roles__c IN :roleSet LIMIT 1] > 0){
		query += ' AND custompicklistfield__c=:level';
		
		enforceLimit = true;
	}
	if(enforceLimit){
		query += ' LIMIT 3';
	}
}
c=database.query(query);

Please mark it as best answer if this resolves your issue.

Regards,
Neha
 
This was selected as the best answer
NehaDabas16NehaDabas16
For test coverage, please create the contact data for both the conditions i.e. records which satisfy Custompicklist__c condition and records which donot.
Also use two different test methods to test these conditions as code will execute only one set of records at once.

You can post your test class code, if you still face issue.
Linda 98Linda 98
Ohh yes...I did some mistake and was struck with code coverage. Was able to figure it out.Thanks a lot for the help.

 
Linda 98Linda 98
Hey hi, I am struck with a small issue. Any help is highly appreciated.
Below is my code which is fetching 5-6(if exists) if the level is not TOP. If the level is TOP, then it is fetching only those(which is OK)
But, if the level is not TOP, I only want to fetch 3 records. I am trying to enforce limit 3 but I am messing with dynamic soql.
if(Roleslist.size() >0){
    Set<String> roleSet = new Set<String>(Roleslist);
        for(Integer i= 0; i<Roleslist.size();i++){
            if(i==0){
            query +=' where account.Active__c=true and roles__c  INCLUDES (\''+Roleslist[0]+'\'';       
            }else{
            query += ',\''+Roleslist[i]+'\'';
            }
           } 
                query += ')';    //LIMIT 3 here   does the work but it is givig me error for next condition.
        Boolean enforceLimit = false;
        if([SELECT count() FROM Contact WHERE  account.Active__c=true AND custompicklistfield__c=:level AND roles__c IN :roleSet LIMIT 1] > 0){
        query += ' AND custompicklistfield__c=:level';
        enforceLimit = true;
    }
    if(enforceLimit){
            query += ' LIMIT 3';
    }
        if(query.contains(' INCLUDES ')){
 
        c=database.query(query);