Wednesday 18 July 2012

Effectiving Aliasing in Force.com Apex

Effective aliasing in Aggregate Queries.
=========================================
How often do you use aliases in Aggregate Queries. Not much? Then please see an example below how important it is.

What would you do If you want to create a map for an aggregate Query and want the grouping field as the key of Map with less effort?
You will try something like below:
Map<string, AggregateResult> mapCountPerUniqueId = new Map<string, AggregateResult>([select case__c, count(Id) from Payer_Involved__c where uniqueId__c != null group by case__c]);
                     
But the above statement will give an exception "Row with null Id at index: 0". So you will abandon the above approach and go for a for loop. But dont give up on the aggregate query's ability yet. With simple aliasing you will not need to write that unnecessary for loop. So, How can it be done? Simple. See the above statement reformed below:

Map<string, AggregateResult> mapCountPerUniqueId = new Map<string, AggregateResult>([select case__c Id, count(Id) piCount from Payer_Involved__c where uniqueId__c != null group by case__c]);
                     
What was the difference you found? Aliases! Isn't it? Yes. They do the magic. If you give "Id" as alias to the first field of the aggregate query, it will simply assume that as the grouping key. And if you observe I used another alias "piCount" which is more meaning full than "expr1" that you would typically use to extract aggregate result values. But, hasn't the word "piCount" made my aggregate query easily understandale.

No comments:

Post a Comment