function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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.

 
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.
NehaDabas16NehaDabas16
Please state your functional requirements. Which contact records do want to fetch?
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
 
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.