Variable does not exist opp.Id - How to pull a count from a SOQL query and set to a variable?

Question

I have an APEX class with a controller extension. This extension pulls in child Service records on a related Opportunity. If you look at the getServices list, you'll see it references opp.Id. This works perfectly.

What I wanted to add was an error catch, because this is all tied to a VF related list. So if no records are pulled in from the query, then I would want it to render a different VF display instead of a blank related list.

To do this, I declared a recordCount integer, and have it set to a database count query. It's essentially the same query as the one in the getQServices query except it's selecting COUNT().

public class serviceViewController {

public final Opportunity opp;
Integer recordCount;


public List<QService__c> getQServices{get; set;}
public serviceViewController(ApexPages.StandardController controller) {

    this.opp = (Opportunity)controller.getRecord();

    //Get count of records pulling in. 
    recordCount = database.countQuery('SELECT COUNT(Id) FROM QService__c WHERE Opportunity__c = :opp.Id LIMIT 1');
    system.debug('The record count is ' + recordCount);

    //Pull in columns for QService__c and load into getQServices list
    getQServices = [SELECT Id, Name, Total_Service_Cost__c
                    FROM QService__c
                    WHERE Opportunity__c = :opp.Id 
                    ];
}}

Where the VF page should just render the list, I get this error instead.

My VF code doesn't reference recordCount because I haven't added the functionality where the controller extension is determining which page to render based off of the recordCount (which means the page should render as normal) because I want to get the recordCount pulled in correctly first. Here's what I know for sure:

1. I know the query is correct because I've tested it by setting the opp.Id to a hardcoded opp id.

2. The developer console in SFDC allows this APEX class to save.

I tried making the recordCount integer a List of object QService__c instead. The error I got there was an illegal assignment from List<AggregateResult> to List<QService__c>, which makes sense.

I'm at a standstill on what the issue might be here. Am I going with best practice here on trying to pull in a count from a query and setting it to a variable or is there something better I should be doing?

Answer 1

Dynamic SOQL has limited support for binding expressions. Currently dynamic SOQL doesn't support binding to anything that isn't directly in the local scope. So if you need to use a . in the binding expression it won't resolve.

Something like this would work:

//Get count of records pulling in. 
Id oppId = opp.Id;
recordCount = database.countQuery('SELECT COUNT(Id) FROM QService__c WHERE Opportunity__c = :oppId LIMIT 1');

Consider voting for the idea: Dynamic SOQL binding expression support to match static SOQL

Better yet. In your case you could switch to an inline SOQL query, which has better binding expression support.

E.g.

recordCount = [SELECT COUNT(Id) FROM QService__c WHERE Opportunity__c = :opp.Id LIMIT 1];

BTW, do you need the trailing LIMIT 1? Your query will be more a check if at least one QService__c exists rather than an actual count. It sounds like that is the case. That will work. Alternatively, you could assign to a list and then check the list size. Either approach will work. So it is more a matter of preference and what makes your intentions clearer:

boolean recordsExist = ![SELECT Id FROM QService__c WHERE Opportunity__c = :opp.Id LIMIT 1].isEmpty();

Or maybe:

List<QService__c> qServiceMatch = [SELECT Id FROM QService__c WHERE Opportunity__c = :opp.Id LIMIT 1];
boolean recordsExist = !qServiceMatch.isEmpty();

See also:

• Bind variable with dotted name not working in dynamic SOQL