Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Angel30Angel30 

count of grandchild records(Opportunity Line Items) on parent(Account)

I have a requirement to count the number of opportunity line items and populate it to the account.
I was thinking to use Aggregate queries,but is it poosible to do it using this.
I am in process of creating this however i am stuck:

public with sharing class opportunityTriggerHandlerClass {
    public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
        Set<Id> oppIdSet = new Set<Id>();
        Set<Id> accIdSet = new Set<Id>();
        for(OpportunityLineItem oli: oliList){
            oppIdSet.add(oli.OpportunityId);
        }
        for(Opportunity oppty : [SELECT ID,AccountId FROM Opportunity WHERE Id IN: oppIdSet]){
             accIdSet.add(oppty.AccountId) ;
        }
        List<AggregateResult> aggr = [SELECT OpportunityId,Opportunity.AccountId,Opportunity.Account.Name,count(Id)oli FROM               

                                                          OpportunityLineItem 
                                                          WHERE Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
    }
}

I face an error that "Field must be grouped or aggregated: OpportunityId".

Can anybody tell me how should we approach here.

Thanks in advance!
Raj2019ssRaj2019ss
Hi Deepshikha Shriwas,
Try the below one 
List<AggregateResult> aggr = [SELECT Opportunity.AccountId, count(Id)oli FROM             
                                                          OpportunityLineItem 
                                                          WHERE Opportunity.AccountId IN:accIdSet 
                                                          GROUP BY Opportunity.AccountId] ;
    }
Pls let me know if you any issues. If it helps select as Solved and best answer.
 
Narender Singh(Nads)Narender Singh(Nads)
Hi Deepshikha,
You can simply get the count as follows: 
integer count = [SELECT count() FROM OpportunityLineItem WHERE Opportunity.AccountId IN:accIdSet ] ;

Let me know if it works.
thanks!
deepshikha shriwas 20deepshikha shriwas 20
Hi Narendra,
 
Since we do data loading, will it be good if we store that in a variable. What i mean is if suppose there are 5 accounts ,so which count will this be stored.

Thanks!
deepshikha shriwas 20deepshikha shriwas 20
Hi Rajeshkumar,

 I made that change!! Thanks.But still it throws an error :

Apex trigger opportunityLineItemTrigger caused an unexpected exception, contact your administrator: opportunityLineItemTrigger: execution of AfterInsert caused by: System.SObjectException: Invalid field Opportunity.AccountId for AggregateResult: Class.opportunityTriggerHandlerClass.countofOliItemsMethod: line 18, column 1  

Below is my code which i wrote:

public with sharing class opportunityTriggerHandlerClass {
    public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
        Set<Id> oppIdSet = new Set<Id>();
        Set<Id> accIdSet = new Set<Id>();
        List<Account> accToUpdate = new List<Account>();
        for(OpportunityLineItem oli: oliList){
            oppIdSet.add(oli.OpportunityId);
        }
        for(Opportunity oppty : [SELECT ID,AccountId 
                                 FROM Opportunity WHERE Id IN: oppIdSet]){
             accIdSet.add(oppty.AccountId) ;
        }
        List<AggregateResult> aggrs = [SELECT Opportunity.AccountId, count(Id)oli FROM OpportunityLineItem 
                                      where Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
        if(aggrs.size() > 0){
            for(AggregateResult aggr : aggrs){
                Account accObj = new Account();
                accObj.Id = (id)aggr.get('Opportunity.AccountId');
                accObj.Count_Of_Opportunity_Line_Items__c =(decimal)aggr.get('oli') ;
                accToUpdate.add(accObj);
            }
             update accToUpdate;
        }
    }
}

Trigger: 
trigger opportunityLineItemTrigger on OpportunityLineItem (after insert) {
    if(trigger.isAfter){
        if(trigger.isInsert){
            opportunityTriggerHandlerClass.countofOliItemsMethod(trigger.new);
        }
    }
}

Is there something which i am missing out.

Thanks in advance to all!!

 
Raj2019ssRaj2019ss
Hi Deepshikha Shriwas,
Please change the below piece oof code and let me know. 
for(AggregateResult aggr : aggrs){
                for(Id accId : accIdSet ){
                Account accObj = new Account();
                accObj.Id = accId;
                accObj.NumberofLocations__c =(decimal)aggr.get('oli') ;
                accToUpdate.add(accObj);
                }
            }
             update accToUpdate;
        }

If it helps select as Solved and best answer.
Narender Singh(Nads)Narender Singh(Nads)
Hi,
Try this code: 
public with sharing class opportunityTriggerHandlerClass {
    public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
        Set<Id> oppIdSet = new Set<Id>();
        Set<Id> accIdSet = new Set<Id>();
        List<Account> accToUpdate = new List<Account>();
        for(OpportunityLineItem oli: oliList){
            oppIdSet.add(oli.OpportunityId);
        }
        for(Opportunity oppty : [SELECT ID,AccountId 
                                 FROM Opportunity WHERE Id IN: oppIdSet]){
             accIdSet.add(oppty.AccountId) ;
        }
        List<AggregateResult> aggrs = [SELECT Opportunity.AccountId, count(Id)oli FROM OpportunityLineItem 
                                      where Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
        if(aggrs.size() > 0){
            for(AggregateResult aggr : aggrs){
                Account accObj = new Account();
                accObj.Id = (id)aggr.get('AccountId');
                accObj.Count_Of_Opportunity_Line_Items__c =(decimal)aggr.get('oli') ;
                accToUpdate.add(accObj);
            }
             update accToUpdate;
        }
    }
}

Trigger: 
trigger opportunityLineItemTrigger on OpportunityLineItem (after insert) {
    if(trigger.isAfter){
        if(trigger.isInsert){
            opportunityTriggerHandlerClass.countofOliItemsMethod(trigger.new);
        }
    }
}

You were getting the error in 
 accObj.Id = (id)aggr.get('Opportunity.AccountId');

Correct version of this would be:  accObj.Id = (id)aggr.get('AccountId');

Let me know if it helps.
Thanks!
 
deepshikha shriwas 20deepshikha shriwas 20
Thanks Narendra .this helps and my code works perfectly fine as of now.
Could you explain me .As i read , it tells that you need to put the alias name that we mention for the field right? Like the way we are mentioning for Oli count field.
 
deepshikha shriwas 20deepshikha shriwas 20
Hi Rajkumar,
 Problem with this is that we would not follow the best practice as its not recommended to use Nested for.
Narender Singh(Nads)Narender Singh(Nads)

Hi  Deepshikha,
Please mark the answer as the best answer so anyone facing similar kind of issue can benefit from this.

And to answer your question,
Yes, you are right we have to mention the alias name to get the value. 
Use system.debug(aggrs);
to see what result you are getting in your aggregate results. It will be clear once you see the debug logs.

Thanks!
deepshikha shriwas 20deepshikha shriwas 20
unable to make this as the best answer.seems i dont have the permission.!! :(  :(
Narender Singh(Nads)Narender Singh(Nads)
You have to choose one answer among all the replies which solved your query. That's how it works.