+ Start a Discussion
MJ09MJ09 

Access field names for an AggregateResult

If I issue a SOQL query that includes aggregate functions, I get back a list of AggregateResult objects. From each of those, I can use ar.get('fieldname') to get the various values. But that assumes I know the names of the field. If I do a Dynamic SOQL query from a VF page controller, where the query is based on input from the user, I may not know the field names ahead of time. How can I determine the fieldnames to use when I want to access the contents of an AggregateResult object?

 

Having read that an AggregateResult is a special type of SObject, I tried using Dynamic Apex to discover the fields:

 

List<AggregateResult> lst = database.query('select count(id), grp__c from myobj__c group by grp__c');

Schema.SObjectType sot = lst.getSObjectType();
Schema.DescribeSObjectResult res = sot.getDescribe();
Set<String> setFields = res.fields.getMap().keyset();
System.debug('Fields are: ' + setFields);

But the only field I get back is "id."  

 

Thanks for your help!

PhaniPhani

Try this:

List<AggregateResult> ar = database.query('select count(id) t, grp__c from myobj__c group by grp__c');
for(AggregateResult arr:ar){ 
  system.debug('Aggregate Result....++++++++++++++++++++++++++++++++++++' + arr);              
  String nn=(String)arr.get('grp__c');              
  Integer vv=(Integer)arr.get('t');
}

MJ09MJ09

Thank you for your response, but that's not exactly what I'm asking. The question is, what do I do if I don't know the column names? Imagine that I'm given a SOQL string to execute, and I don't know what the field names are. I can't call ar.get('fieldname') because I don't know what the name of the field is.

 

Is there any way to determine the field names for an AggregateResult at runtime?

jdajda

You can use the following syntax

 

ar.get('expr0')

 

the value 0 is for the first aggregated column. And 'expr1' for the second column and so on.

MJ09MJ09

JDA,

 

Thank you for your reply to this aging thread!

 

Refering to the aggregated columns with expr0, expr1, etc. doesn't always work. If the SOQL query was written using an alias for one of the aggregate results, that column will use the alias name, not expr0.

 

My original question said, "If I do a Dynamic SOQL query from a VF page controller, where the query is based on input from the user, I may not know the field names ahead of time." Imagine that I have a VF page in which the user types in a random SOQL query. How can my Apex code determine the names of the columns that are returned?

 

Thanks.

h8r41dh8r41d

Can anyone answer this question?

 

I'm trying to write a little query builder so I can use that instead of the crappy IDE query window that doesn't let you interact with the results

Starz26Starz26

You have to know the names of the fields to buld the query right?

 

Why not put the names in a set or list and use itenerate over those values to get the fields from the aggregate query.

MJ09MJ09

> You have to know the names of the fields to buld the query right?

 

Not necessarily. What if you're letting the user type in a query, like the IDE and Force.com Explorer allow?

h8r41dh8r41d

MJ

 

I just ended up just writing a miniature query compiler to extrapolate the field list.