Nested Dynamic SOQL Query

Question

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.

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
– Adrian Larson ♦, Commented Jun 26, 2019 at 13:42

sfdcfox · Accepted Answer · 2019-03-18 14:51:20Z

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)';

answered Mar 18, 2019 at 14:51

sfdcfox

1

@ThomasPete you're welcome! P.S. String.isNotBlank also checks for null values, no need to check that separately in your code.

sfdcfox
– sfdcfox

2019-03-18 14:56:23 +00:00
Commented Mar 18, 2019 at 14:56

Add a comment |

Adrian Larson · Accepted Answer · 2019-03-18 14:52:10Z

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'

answered Mar 18, 2019 at 14:52

Adrian Larson♦

153k38 gold badges251 silver badges438 bronze badges

Thanks, Adding a context variable did it!

Anand Sharma
– Anand Sharma

2019-03-18 14:55:36 +00:00
Commented Mar 18, 2019 at 14:55

Add a comment |

Stack Exchange Network

Nested Dynamic SOQL Query

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Nested Dynamic SOQL Query

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions