4

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.

7

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)';
  • Worked! Thanks. – Thomas Pete Mar 18 at 14:53
  • @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 at 14:56
6

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'
  • Thanks, Adding a context variable did it! – Thomas Pete Mar 18 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.