5

I am Trying to write a dynamic SOQL Query with a nested query.

@AuraEnabled
public static List<Books__c> filterRecords(String bookNumber, String PDCN, String Brand, String Type){
    System.debug('bookNumber__c->'+bookNumber__c);
    System.debug('PDCN->'+PDCN);
    System.debug('Brand->'+Brand);

    String query = 'SELECT Id,Content__c,ForecastWeekIndicator__c,Generic_PDCN__c,isExpired__c, '+
        'BrandStandardName__c,DPForecast_BaseUnits__c,ForecastStatus__c,Generic_DateTime__c, '+
        'Type__c,OperationalForecast_BaseUnits__c,PackageStandardName__c,bookNumber__c__c '+
        'FROM Books__c WHERE Type__c =:Type '+
        'AND Id NOT IN (SELECT Books__c FROM User_Specific_Books__c '+ 
        'WHERE Books_Delete__c = TRUE AND User__c = : UserInfo.getUserId())';
    if(string.isNotEmpty(bookNumber__c) && bookNumber__c!=null){
        query = query + ' AND bookNumber__c__c =: bookNumber__c';
    }
    if(string.isNotEmpty(PDCN) && PDCN != null){
        query = query + ' AND Generic_PDCN__c =: PDCN';
    }
    if(string.isNotEmpty(Brand) && Brand != null){
        query = query + ' AND BrandStandardName__c =: Brand';
    }
    System.debug('query--->'+query);
    List<Notification__c> lst;

    try{
        lst = Database.query(query);
        system.debug('List--->'+lst);

    }catch(Exception e){
        system.debug(e.getMessage());
    }

    if(!lst.isEmpty()){
        return lst;
    }
    else
        return null;
    }
}

and, I am getting exception expecting a right parentheses, found '('. Any explanation would be appreciated.

1
  • Thomas, If either answer below addressed your problem, please consider accepting it by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either. – Adrian Larson Jun 26 '19 at 13:42
8

You can't use actual Apex in dynamic queries. Only "simple" variables are allowed. The problem is in the following line.

    'WHERE Books_Delete__c = TRUE AND User__c = : UserInfo.getUserId())';

Assign the value to a variable first:

Id userId = UserInfo.getUserId();
...
    'WHERE Books_Delete__c = TRUE AND User__c = :userId)';
1
  • @ThomasPete you're welcome! P.S. String.isNotBlank also checks for null values, no need to check that separately in your code. – sfdcfox Mar 18 '19 at 14:56
7

You can't use dot reference in dynamic query. Here is your problem:

'WHERE Books_Delete__c = TRUE AND User__c = : UserInfo.getUserId())'

You need to add some context variable to store the Id and then change it to:

'WHERE Books_Delete__c = TRUE AND User__c = :runningUserId'
1
  • Thanks, Adding a context variable did it! – Anand Sharma Mar 18 '19 at 14:55

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.