Why is the benefit of using the aggregate query in Salesforce?
I am wondering if there are any benefits of using Aggregate Query in Salesforce. Let's say we have 2 code snippets here.
Let's say accIdSet is a collection account Id we are working on.
CODE 1 :
for (AggregateResult agg : [
SELECT Count(ID) cnt, AccountId
FROM Contact
WHERE AccountId IN :accIdSet
GROUP BY AccountId
]) {
Account acc = new Account(Id = (String) agg.get('AccountId'));
acc.Total_Contact__c = (Integer) agg.get('cnt');
updateAccList.add(acc);
}
CODE 2:
for (Account acc : [
SELECT Id, (
SELECT Id
FROM Contacts
)
FROM Account
WHERE Id IN :accIdSet
]) {
Account accObj = new Account(Id = acc.Id);
accObj.Total_Contact__c = acc.Contacts.size();
updateAccList.add(accObj);
}
Both do the same thing, count the number of contacts and update the Total_Contact__c field on the Account record.
I have a basic idea of Aggregate Query and its aggregate functions but are there any benefits of using this.
Salesforce uses some confusing terminology around this topic. For clarity...
An aggregate query in Salesforce (as far as governor limits and error messages seem to go) is a parent-child subquery (a.k.a. a "left outer join" in more standard SQL)
An aggregate function is something like COUNT(), MAX(), AVG(), etc...Aggregate functions can only be applied on the outermost query, and most (if not all) require you to also use GROUP BY. This causes the query to return a List rather than a List.
Both parent-child subqueries and aggregate functions have their uses. In the particular example that you've given, the advantage of using COUNT() is that you don't need to worry about the number of records returned. It'll get you the number you're looking for with minimal fuss.
With a parent-child subquery, however, if you have enough child records you won't be able to simply call parent.children.size(). At a certain point, Salesforce will try to call queryMore (internally) and you'll be greeted with an error message telling you to iterate over the child records.
That means that your simple parent.children.size() becomes (pseudocode)
for(parent parent query]){
List childList;
for(child arent.children){
childList.add(child);
}
childList.size();
}
So in this case, the aggregate functions end up being less work (SOQL does more of the work for you). Things like doing your own roll ups are a good use case for aggregate functions as well. In other cases, the parent-child subquery approach may make the rest of your code simpler.
At the end of the day, it's about doing whatever makes your life easier. If there's a reasonable way to foist some of the work onto SOQL (or another tool/feature) that would normally be done via apex, it's worth looking into.
Course: Salesforce
Question 38. What must I do when system.queryexception: list has no rows for assignment to sobject?
Question Description: I have this controller but I am getting this error
System.QueryException: List has no rows for assignment to SObject
public class MyController {
private final Account account;
public MyController() {
account = [SELECT Id, Name, Site FROM Account
WHERE Id = :ApexPages.currentPage().getParameters().get('id')];
}
public Account getAccount() {
return account;
}
public PageReference save() {
update account;
return null;
}
}