Issue related Dynamic SOQL query

Question

I have written a dynamic query for filter data at run time based on the different condition.

string squery='Select id, SVMXC__Serial_Number__c, SVMXC__Serial_Number__r.Name, SVMXC__Service_Order__r.Name, SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c, SVMXC__Service_Order__r.SVMXC__Work_Order_Scheduling_Status__c, '+ 
                'SVMXC__Service_Order__r.SVMXC__Order_Type__c, SVMXC__Service_Order__r.Order_Subtype__c, SVMXC__Service_Order__r.FLECHA_Bill_Total__c, SVMXC__Service_Order__r.FLECHA_Earnings__c, '+
                'SVMXC__Service_Order__r.SVMXC__Order_Status__c from SVMXC__Service_Order_Line__c '+ 
                'where SVMXC__Serial_Number__c IN :FilterProductDataId and SVMXC__Service_Order__r.SVMXC__Order_Type__c=\'service\' '+ 
                'and SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c>= :startDate '+ 
                'and SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c<= :endDate';

FilterProductDataId is the set of Ids and startDate and endDate is the dateTime field I have tried a lot but this query does not give any data I don't know How I can solve it can you please help me?

public class ServiceWorkOrderListCtrl {
public static list<SVMXC__Service_Order_Line__c> workDetail;
public static string contId =null;

@AuraEnabled
public static list<SVMXC__Service_Order_Line__c> FindInstallProducts(string RecordID ){
    contId=RecordID;        
    set<Id> installProductId=new set<Id>(); 
    list<SVMXC__Service_Order__c> data1 = new list<SVMXC__Service_Order__c>();
    try{
        SVMXC__Service_Contract__c serviceContract=[select id,  SVMXC__Start_Date__c, SVMXC__End_Date__c from SVMXC__Service_Contract__c where id=:RecordID];
        date startDate=serviceContract.SVMXC__Start_Date__c;
        date endDate=serviceContract.SVMXC__End_Date__c;
        
        
        list<SVMXC__Service_Contract_Products__c> coverProduct= [SELECT Id,SVMXC__Installed_Product__c, SVMXC__Service_Contract__c
                                                                 FROM SVMXC__Service_Contract_Products__c 
                                                                 where SVMXC__Service_Contract__r.id=:RecordID];
        
        for(SVMXC__Service_Contract_Products__c Data:coverProduct){
            if(Data.SVMXC__Installed_Product__c !=null){
                installProductId.add(Data.SVMXC__Installed_Product__c);
            } 
            
        }
        set<id> FilterProductDataId=new set<id>();
        
        list<SVMXC__Installed_Product__c> FilterProductData=[select Id,Name, RecordType.Name from SVMXC__Installed_Product__c where Id IN :installProductId and RecordType.Name= 'EEE Life Sciences'];
        
        for(SVMXC__Installed_Product__c filterId:FilterProductData){
            FilterProductDataId.add(filterId.id);
        }
        
        system.debug('FilterProductDataId'+FilterProductDataId);
        
        workDetail= [Select id, SVMXC__Serial_Number__r.Name, SVMXC__Service_Order__r.Name , SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c, SVMXC__Service_Order__r.SVMXC__Work_Order_Scheduling_Status__c, 
                     SVMXC__Service_Order__r.SVMXC__Order_Type__c, SVMXC__Service_Order__r.Order_Subtype__c ,SVMXC__Service_Order__r.FLECHA_Bill_Total__c,SVMXC__Service_Order__r.FLECHA_Earnings__c,
                     SVMXC__Service_Order__r.SVMXC__Order_Status__c from SVMXC__Service_Order_Line__c 
                     where SVMXC__Serial_Number__r.id =:FilterProductDataId and SVMXC__Service_Order__r.SVMXC__Order_Type__c='service' 
                     and SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c>=:startDate 
                     and SVMXC__Service_Order__r.SVMXC__Scheduled_Date_Time__c<=:endDate];

    }
    catch(Exception e){
        System.debug('Message--'+e.getMessage()+'LineNumber--'+e.getLineNumber()+'getCause--'+e.getCause());
    }
    return workDetail;
}

}

This above code is working fine for me and the workDetail list gives me as accurate data as I want.

But I want to write this query in Dynamic because in some cases I want to add some extra filter conditions at run time.

Answer 1

The WHERE clause is different in the dynamic query vs the static one.

WHERE SVMXC__Serial_Number__c IN :FilterProductDataId

WHERE SVMXC__Serial_Number__r.id = :FilterProductDataId