1

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.

enter image description here

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?

  • It looks like you're trying to reference opp in your markup. Read up on property syntax and change public final Opportunity opp; to public Opportunity opp { get; private set; } – Adrian Larson Mar 12 '18 at 19:16
  • I suspect it may be an issue with dynamic SOQL binding expressions. Try assigning the ID to an intermediate variable and binding to that. E.g. WHERE Opportunity__c = :oppId LIMIT 1 – Daniel Ballinger Mar 12 '18 at 19:16
  • FYI, your List<> types were messed up in the question because you used boldface instead of code formatting - use back ticks to make sure that comes through. – David Reed Mar 12 '18 at 19:16
5

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:

  • Didn't even spot the dynamic query. +1 – Adrian Larson Mar 12 '18 at 19:26
  • @AdrianLarson It's definitely something that has caught me out in the past. – Daniel Ballinger Mar 12 '18 at 19:31
  • What would you suggest if it was a check if at least one QService__c exists? That's what I was hoping to accomplish with the LIMIT 1, so if at least 1 came in, then that would be enough to determine what VF page should load. Also, the above info on Dynamic SOQL limit for binding expressions is incredible to know, thanks for sharing that! – Kruse in Salesforce Mar 12 '18 at 19:32
  • Also harder to see on mobile. – Adrian Larson Mar 12 '18 at 19:35
  • 1
    @KruseCollins Is should work to check if the record exists. You don't need to use the Count(). A LIMIT 1 and isEmpty() on a list would also be sufficient. – Daniel Ballinger Mar 12 '18 at 20:37

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.