Grouped Query Cannot Use Child Relationship in Remote Action Apex Class

Question

I am trying to count the number of account names that occur in my soql statement. Ultimately I have a map that shows all of the accounts near a technician and I want to show the number (count) of accounts on the map. When I try to create an aggregate expression on the SOQL string in the remote action portion of the apex class, I keep getting the error "Grouped Query Cannot Use Child Relationship in SELECT List." If I can't use the Count function in the SOQL String, is there another way I can do this?

 public with sharing class NearbyTechnicianController {

   public Account account { get; set; }
   public SVMXC__Service_Group_Members__c sgm {get;set;}
   public SVMXC__Installed_Product__c insp {get;set;}
   public Integer NumAccts {get;set;}



    // constructor
    public NearbyTechnicianController() {
        sgm = [select id, name, SVMXC__Street__c, SVMXC__City__c, SVMXC__State__c, SVMXC__Zip__c, Geolocation__Latitude__s, Geolocation__Longitude__s from SVMXC__Service_Group_Members__c where id = :ApexPages.currentPage().getParameters().get('id')];

        account = [select Id, Name, CustomerMasterId__c, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, Geolocation__Latitude__s, Geolocation__Longitude__s, (Select SVMXC__Status__c, Name, Primary_FSE__c, SVMXC__Product__c, Model_Code__c from R00N70000001hzZ0EAI__r) 
                  from Account limit 1];

        insp = [select Id, Name, Model_Code__c from SVMXC__Installed_Product__c limit 1];          

        NumAccts = getNearbyTech.Size();

    }


    @RemoteAction
    public static List<Account> getNearbyTech(Decimal latitude, Decimal longitude, String TechName) {
        String fseLike = TechName + '%';
        String q = 'select Id, Name, CustomerMasterID__c, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, Geolocation__Latitude__s, Geolocation__Longitude__s, (Select SVMXC__Status__c, Name, Primary_FSE__c, SVMXC__Product__c, Model_Code__c from R00N70000001hzZ0EAI__r) from Account ';
        q += 'where DISTANCE(Geolocation__c, GEOLOCATION( ';
        q += String.valueOf(latitude) + ', ' + String.valueOf(longitude);
        q += ' ), \'km\') < 200';
        q += 'AND id in (select SVMXC__Company__c from SVMXC__Installed_Product__c ';
        q += 'where (Primary_FSE__c like :fseLike) AND (SVMXC__Status__c like \'Installed%\') AND ((Model_Code__c like \'DSD%\') OR (Model_Code__c like \'ADV%\') OR (Model_Code__c like \'EDG%\')))';

        return Database.query(q);  

    }  


}

Answer 1

You just need to know the number of accounts the query returns? Why not use getNearbyTech.size()? No need to use an aggregate expression.

edited to add getNearbyTech is declared as a list, so getNearbyTech.size() will get you the number of elements in the list. The ".size()" method call takes no parameters. You can read more about list methods here

To reference the size in a visualforce page, you'd need to declare getter/setter methods in your controller class:

public Integer NumAccts {get; set;}

and set the value at some point (usually in the constructor)

 NumAccts = getNearbyTech.Size();

Then you can reference the count in a Visualforce page like this:

{!NumAccts}

The only think I'm unclear about is whether the "@remoteaction" affects whether the size will be available when the constructor runs. If not, there may be an extra hoop to jump through to refresh the page when the query actually complete. Perhaps someone else here will let us know.

second edit The problem here is @remoteaction. This answer explains why. Short version - @remoteaction calls can't update the Visualforce page view state.

I think Keith C is on the right track in his comment. You might need to run a separate query that does not use the child relationship and use a for loop to examine and count the relevant records yourself. This could be done in the constructor and the result stored in NumAccts for use in Visualforce as shown above.