2

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

    }  


}
  • I suggest you use two queries: one that does the aggregation without the "Child Relationship in SELECT List" and a second one with it if you need that data elsewhere in your logic. – Keith C Sep 3 '14 at 8:15
  • Don't I need to include the child relationship in the select list to get the correct amount of accounts to show up? – NewbieDeveloper27 Sep 4 '14 at 15:04
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.

| improve this answer | |
  • So if i would to do getNearbyTech.size(Decimal latitude, Decimal longitude...) how would I reference that in my visualforce page? – NewbieDeveloper27 Sep 3 '14 at 13:57
  • so i made your changes and edited my code above. However, now I get an error stating that the varibale getNearbyTech does not exist. – NewbieDeveloper27 Sep 3 '14 at 14:52

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.