Variable does not exist: portalUserInfo.conAccount.Id in Query

Question

Please help with this error:

String query = 'SELECT Project__c,'
              + ' Project__r.Testing_Submitted__c,'
              + ' Project__r.Project_Sub_Phase__c,'
              + ' Project__r.DRD_Submited__c,'
              + ' Protected_Application__r.DRD_Completed__c,'
              + ' Protected_Application__c,'
              + ' Project__r.Id,'
              + ' Project__r.Name,'
              + ' Project__r.Project_Type__c,'
              + ' Project__r.Remark__c,'
              + ' Project__r.Record_Type_Name__c,'
              + ' Project__r.RecordTypeId,'
              + ' Project__r.Project_Actual_Completion__c,'
              + ' Protected_Application__r.Application__c,'
              + ' Project__r.Project_Phase__c,'
              + ' Project__r.Ready_for_Testing__c,'
              + ' Project__r.recordType.DeveloperName,'
              + ' Project__r.Project_Sub_Status__c,'
              + ' Project__r.Certification_was_submitted__c,'
              + ' Project__r.Customer_confirmed_launch_date__c,'
              + ' Protected_Application__r.Application_Type__c'
              + ' FROM ProjectAppLink__c ';


projectList = Database.query (query + ' WHERE Project__r.Project_Status__c = \'Open\' and Project__r.Account__c = :portalUserInfo.conAccount.Id and Project__r.Online_Project__c = true and Project__r.recordType.DeveloperName in: settingMap.keySet()');

Edit: First issue was resolved thanks to @Ratan - changed the Map to a Set.

I got another error 'Variable does not exist: portalUserInfo.conAccount.Id' but it does exist. When I try to make a regular query it's working just fine.

I then tried to revise the code:

        projectList = Database.query (query + ' WHERE Project__r.Project_Status__c = \'Open\' and Project__r.Account__c = :' 
        + portalUserInfo.conAccount.Id + ' and Project__r.Online_Project__c = true and Project__r.recordType.DeveloperName in: settingSet');

But now I get: 'Only variable references are allowed in dynamic SOQL/SOSL. '

Answer 1

Unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string. In doing so will result in a Variable does not exist error. Exactly what you are seeing.

Problem:

:portalUserInfo.conAccount.Id

Solution:

Resolve the variable field into an Id and use the id in your dynamic SOQL query.

Id conAccountId = portalUserInfo.conAccount.Id;
projectList = Database.query (query
    + ' WHERE Project__r.Project_Status__c = \'Open\''
    + ' and Project__r.Account__c = :conAccountId' 
    + ' and Project__r.Online_Project__c = true'
    + ' and Project__r.recordType.DeveloperName in: settingSet');