1

I have a following method which returns result based on the dynamic SOQL query. It was running fine until I added additional filter criteria where I am checking Id != : ClosedCleanupId Below is the method

 public static List < sObject > fetchLookUpValues(String searchKeyWord, String ObjectName, String ClosedCleanupId) {
   // system.debug('ObjectName-->' + ObjectName);
   // 
    Id closedId = Id.valueOf(ClosedCleanupId);
    set<Id> setclosedId = new set<Id>();
    setclosedId.add(closedId);
    String searchKey = searchKeyWord + '%';
    Set<String> closedStages = new set<String> {'Closed - Clean Up','Closed - Lost'};
    List < sObject > returnList = new List < sObject > ();
    List<sObject> searchResult = new List<sObject> ();
    String sQuery =  'select id, Name,Deal_Registration_Id__c from ' +ObjectName + ' where id NOT IN : setclosedId AND stageName NOT IN :closedStages AND Deal_Registration_Id__c LIKE: searchKey order by createdDate DESC limit 5';
    List < sObject > lstOfRecords = Database.query(sQuery);
    for (sObject obj: lstOfRecords) {
       returnList.add(obj);
    }
    return returnList;
}

Can anyone point me what's the issue with dynamic query?

EDIT 1:- It seems value of ClosedCleanupId is in double quotes , like "006..." , So Id.valueOf(ClosedCleanupId) throws error. Is there a way I can replace double quotes in Apex or in JS? This method is auraenabled and called from lightning component JS controller.

8

In Dynamic SOQL, you need to pay attention to spaces. There should be no space between : and what follows.

Bad

id NOT IN : setclosedId 

Good

id NOT IN :setclosedId 

Spacing is important when building dynamic query strings, as the system is far less lenient in that case.

  • 5
    Never knew that. Must have been consistent/lucky up to now. – Keith C Dec 9 '19 at 20:03
  • I am still getting the error. It seems value of String ClosedCleanupId is in double quotes like - "006... " , that's the reason Id.valueOf(ClosedCleanupId) returns error. To be more specific to my query- Is there a way I can convert double quotes to single quotes in apex? ClosedCleanupId value is getting from JS controller method which has string type of the attribute – Manish Anand Dec 9 '19 at 21:00
  • @sfdcfox I've just tested but even with a space my dynamic query runs without error – dibocor Dec 10 '19 at 0:03
  • @dibocor Odd. I remember that being a thing. Maybe it was something else I'm thinking of, or maybe they fixed it. Hard to tell, at this point. – sfdcfox Dec 10 '19 at 4:18
  • @ManishAnand Your ID value shouldn't have quotes in it. Perhaps you should approach this from the other direction: why are there quotes in your ID value? There's something probably wrong with the JS, not the Apex. – sfdcfox Dec 10 '19 at 4:18
0

How about just leaving the ids as strings? Something like:

public static List <sObject> fetchLookUpValues(String searchKeyWord, String objectName, String closedCleanupId) {
  String searchKey = searchKeyWord + '%';
  Set<String> closedStages = new set<String> {'Closed - Clean Up','Closed - Lost'};
  String sQuery =  'SELECT Id, Name,Deal_Registration_Id__c FROM ' + objectName + ' WHERE Id != :closedCleanupId AND stageName NOT IN :closedStages AND Deal_Registration_Id__c LIKE :searchKey ORDER BY createdDate DESC LIMIT 5';
  return Database.query(sQuery);
}

This method could probably benefit from some user input checking and perhaps try to examine the result of the query, but this would be a start.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.