How Can make my query dynamic, I found error inside this query? System.QueryException: expecting a colon, found 'Regional'

Question

 String userFields = 'user.id, user.profile.name';
    String profileName = 'Regional CS Team Member';        
    String userObject = 'user, user.profile';
    String userCondition = 'user.profile.name = "Regional CS Team Member"';
    String userANDCondition = 'user.IsActive= TRUE ';
    String userQuery = 'SELECT '+ userFields + ' FROM ' + userObject + ' WHERE '+ userCondition + ' AND ' + userANDCondition + ' ORDER BY name ASC ';
    System.debug(userQuery);

     //put the list for get the user id 
    List<User> lstOfUsers = Database.query(userQuery);

    String oppFields = 'Id, Name, Amount ';
    String oppObject = ' Opportunity ';
    String oppCondition = ' CreatedById IN :  ';
    String oppANDCondition = ' AND Amount != Null AND Amount != 0 ';        
    String oppQuery = ' SELECT '+ oppFields+' FROM '+oppObject+' WHERE '+ oppCondition + lstOfUsers + oppANDCondition;
    System.debug(oppQuery);
    
    //get the records of opportunity
   List<Opportunity> fetchAllRelatedOpp = Database.query(oppQuery);

///////////////////////////////////////////////////////////////////////

I am using here my dynamic query to found out the user records and opportunity records to get the data

I am going to provide link of my another question enter link description here

public with sharing class WeeklyEngagemnt{    
 static map<string,integer> weekDaysToNumber = new map<string,integer>{ 'Monday'=>0,
                                                                        'Tuesday'=>1,
                                                                        'Wednesday'=>2,
                                                                        'Thursday'=>3,
                                                                        'Friday'=>4,
                                                                        'Saturday'=>5,
                                                                        'Sunday'=>6};         
//get the weeks from the method of 'getMyDates'                                                                    
 public static List<date> getMyDates(string dayOfWeekName, integer month, integer year){
    List<date> returnList = new List<date>();
    date d = date.newInstance(year,month,1);
    d=d.addDays(math.mod(7-dayOfWeek(d)+weekDaysToNumber.get(dayOfWeekName),7));
    do{
        returnList.add(d);
        d=d.addDays(7);
    } while(d.month()==month);
    return returnList;
}
public static integer dayOfWeek(date d){
    return math.mod(date.newInstance(1900,1,8).daysBetween(d),7); 
}
/*
public WeeklyEngagemnt(){
    String userFields = 'user.profile.name';
    String profileName = 'Regional CS Team Member';
    String oppFields = 'Name,Amount';
} //how we can user the string varible inside our method fetchAllRelatedOpportunity 
*/
public static void fetchAllRelatedOpportunity(){
    //Create list of date 
    List<Date> lstOfDates = WeeklyEngagemnt.getMyDates('Monday',4,2023);        
    Decimal total = 0;  
    String userFields = 'user.id, user.profile.name';
    String profileName = 'Regional CS Team Member';        
    String userObject = 'user, user.profile';
    String userCondition = 'user.profile.name =: ';
    String userANDCondition = 'user.IsActive= TRUE ';
    String userQuery = 'SELECT '+ userFields + ' FROM ' + userObject + ' WHERE '+ userCondition + profileName + ' AND ' +
                        userANDCondition + ' ORDER BY name ASC ';
    System.debug(userQuery);
    
    //put the list for get the user id 
    List<User> lstOfUsers = Database.query(userQuery);
    
    String oppFields = 'Id, Name, Amount ';
    String oppObject = ' Opportunity ';
    String oppCondition = ' CreatedById IN :  ';
    String oppANDCondition = ' AND Amount != Null AND Amount != 0 ';        
    String oppQuery = ' SELECT '+ oppFields+' FROM '+oppObject+' WHERE '+ oppCondition + lstOfUsers + oppANDCondition;
    System.debug(oppQuery);
    
    //get the records of opportunity
   List<Opportunity> fetchAllRelatedOpp = Database.query(oppQuery);  
    
    //Updated opportuntiy list
    List<Opportunity> lstOpp = new List<Opportunity>();         
     
    //Map of user Id with list of Dates
   Map<Id,List<Date>> userByweekClosedWonValue = new Map<Id,List<Date>>();       
    for(User objUser: lstOfUsers){
        if(lstOfUsers.size()>0){
            userByweekClosedWonValue.put(objUser.Id, lstOfDates);               
        }
    }
    if(fetchAllRelatedOpp.size()>0){
         for( Opportunity objOppo : fetchAllRelatedOpp){           
            total = total+objOppo.Amount;     
             lstOpp.add(objOppo);                
        }            
    }    
    
    system.debug('listOfOppo ===> ' + lstOpp); 
    System.debug('Total: '+total);

} 

}

Answer 1

The first thing to ask is whether or not this query needs to be dynamic. With what you've shown thus far, the answer to that question is definitely "no, this does not need to be a dynamic query".

Dynamic queries introduce additional complications, and any errors in them will only be found at run-time (as opposed to at compile time).

Assuming that you're trying to learn here rather than seriously trying to use this query for actual work...

The main issue

is with String userCondition = 'user.profile.name = "Regional CS Team Member"';
SOQL (and Apex, for that matter) doesn't use double quotes to enclose strings.

If you need to put a string inside of a string, then you need to escape the single quotes with a backslash.
String userCondition = 'user.profile.name = \'Regional CS Team Member\'';

The backslash tells Apex (or rather, the compiler) "yes, this is a [thing], but don't treat it how you normally would".

Other issues

• You don't need to prefix "user" to fields of the User SObject when the primary SObject for your query is User
• Using 'user, user.profile' for the FROM in your first query is unnecessary. It can be used to reduce typing when you're working with multiple levels of parent records, but you're not using that here. You should only query on User here
• You've already created a string to hold "Regional CS Team Member", so userCondition should use a simple variable bind, e.g. String myCondition = 'Name = :otherVariable';.
• String oppCondition = ' CreatedById IN : '; is wrong. The variable that you're using in the variable bind must be part of the string.

Variable binds in dynamic SOQL also have additional restrictions (namely, that you cannot use dot notation to try to reference a specific field or property). In my own testing, it appears that CreatedById IN :lstOfUsers should work (as would passing in something like a List<String>).

Final thoughts

String concatenation like this is messy and prone to issues. Dynamic SOQL needs whitespace between the keywords and stuff like field names and sobject names. While you do appear to have that here, it's something that's easy to miss and trips many people up.

Instead of that, using String.format() can be a better idea. To give you an idea of how you might use that...

List<String> fieldAPINames = new List<String>{'Id', 'Profile.Name'};
String profileName = 'Regional CS Team Member'; 
String queryString = String.format(
    // The {0}, {1}, etc... are placeholders, which are substituted with that
    //   index in the subsequent list that you provide to this method
    'SELECT {0} FROM {1} WHERE {2} ORDER BY {3}',
    new List<Object>{
        // Simple string lists can be used with a simple String.join()
        // Other things (especially dates in the WHERE clause) would
        //   need more special handling
        String.join(fieldAPINames, ', '),
        // Plain 'ol strings are fine
        'User',
        // Simple binds are fine
        'IsActive = true AND Profile.Name = :profileName',
        'Name ASC'
    }
);

Answer 2

public with sharing class WeeklyEngagemnt{    
 static map<string,integer> weekDaysToNumber = new map<string,integer>{ 'Monday'=>0,
                                                                        'Tuesday'=>1,
                                                                        'Wednesday'=>2,
                                                                        'Thursday'=>3,
                                                                        'Friday'=>4,
                                                                        'Saturday'=>5,
                                                                        'Sunday'=>6};         
//get the weeks from the method of 'getMyDates'                                                                    
 public static List<date> getMyDates(string dayOfWeekName, integer month, integer year){
    List<date> returnList = new List<date>();
    date d = date.newInstance(year,month,1);
    d=d.addDays(math.mod(7-dayOfWeek(d)+weekDaysToNumber.get(dayOfWeekName),7));
    do{
        returnList.add(d);
        d=d.addDays(7);
    } while(d.month()==month);
    return returnList;
}
public static integer dayOfWeek(date d){
    return math.mod(date.newInstance(1900,1,8).daysBetween(d),7); 
}
/*
public WeeklyEngagemnt(){
    String userFields = 'user.profile.name';
    String profileName = 'Regional CS Team Member';
    String oppFields = 'Name,Amount';
} //how we can user the string varible inside our method fetchAllRelatedOpportunity 
*/
public static void fetchAllRelatedOpportunity(){
    //Create list of date 
    List<Date> lstOfDates = WeeklyEngagemnt.getMyDates('Monday',4,2023);        
    Decimal total = 0;        
    String userFields = 'user.id, user.profile.name';
    String profileName = 'Regional CS Team Member';        
    String userObject = 'user, user.profile';
    String userCondition = 'user.profile.name = \'Regional CS Team Member\'';
    String userANDCondition = 'user.IsActive= TRUE ';
    String userQuery = 'SELECT '+ userFields + ' FROM ' + userObject + ' WHERE '+ userCondition + ' AND ' +
                        userANDCondition + ' ORDER BY name ASC ';
    System.debug(userQuery);
    
    //put the list for get the user id 
    List<User> lstOfUsers = Database.query(userQuery);        
    String oppFields = 'Id, Name, Amount ';
    String oppObject = ' Opportunity ';
    String oppCondition = ' CreatedById IN : lstOfUsers ';
    String oppANDCondition = ' AND Amount != Null AND Amount != 0 ';        
    String oppQuery = ' SELECT '+ oppFields+' FROM '+oppObject+' WHERE '+ oppCondition + oppANDCondition;
    System.debug(oppQuery);
     
    //get the records of opportunity
   List<Opportunity> fetchAllRelatedOpp = Database.query(oppQuery);  
    
    //Updated opportuntiy list
    List<Opportunity> lstOpp = new List<Opportunity>();         
     
    //Map of user Id with list of Dates
   Map<Id,List<Date>> userByweekClosedWonValue = new Map<Id,List<Date>>();       
    for(User objUser: lstOfUsers){
        if(lstOfUsers.size()>0){
            userByweekClosedWonValue.put(objUser.Id, lstOfDates);               
        }
    }
    if(fetchAllRelatedOpp.size()>0){
         for( Opportunity objOppo : fetchAllRelatedOpp){           
            total = total+objOppo.Amount;     
             lstOpp.add(objOppo);                
        }            
    }    
    
    system.debug('listOfOppo ===> ' + lstOpp); 
    System.debug('Total: '+total);

} 

}

Now my Dynamic query working fine