function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
D-VacD-Vac 

Apex for Largest number of Child Records Types to a Master Record?

Hi, 

 

I have an object which is a child of an opportunity. "Child object".  The Child Object has a field called "Blah" which is a lookup to another object called "Blah".  

 

I am trying to come up with an Apex class to determine -- per opportunity -- which Blah record shows up the most number of times in the Child Object records. 

 

So for example: 

 

Opportunity A:

Child 1: Blah = Hat

Child 2: Blah = Coat

Child 3: Blah = Hat

Child 4: Blah = Hat

Child 5: Blah = Jim

 

How do you write a class that returns for Opportunity A, the value "Hat"?

 

Is it aggregateresult group by "Blah"?  How do you return the "Blah" value of the max rows of Blah value (and not just the number of Child Records for that Blah value)?  Do you return the aggregateresults and then query those results with an order by?

 

I figured there is probably a way to go about this which more experienced developers know.  

 

Any help?  I tried scowering the boards but couldn't quite find anything similar. 

 

Thank you!

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

I don't think you can do this through a single query, as you want the maximum of  a count of records, which is kind of a double aggregation.  I think you'll need to execute an aggregate query and process the results.  Using group by and count should give you the breakdown - something like;

 

Id maxBlahId;
Integer maxBlahCount=0;
for (AggregateResult ar: [select id blah, Opportunity__c, COUNT(Id) blahCount
        from Blah__c
        where Opportunity__c=:oppId
        GROUP BY id])
{
   Integer blahcount=(Integer) ar.get('blahCount');
   if (blahCount>maxBlahCount)
   {
      maxBlahCount=blahCount;
      maxBlahId=(Id) ar.get('blah);
   }
}

 

All Answers

bob_buzzardbob_buzzard

I don't think you can do this through a single query, as you want the maximum of  a count of records, which is kind of a double aggregation.  I think you'll need to execute an aggregate query and process the results.  Using group by and count should give you the breakdown - something like;

 

Id maxBlahId;
Integer maxBlahCount=0;
for (AggregateResult ar: [select id blah, Opportunity__c, COUNT(Id) blahCount
        from Blah__c
        where Opportunity__c=:oppId
        GROUP BY id])
{
   Integer blahcount=(Integer) ar.get('blahCount');
   if (blahCount>maxBlahCount)
   {
      maxBlahCount=blahCount;
      maxBlahId=(Id) ar.get('blah);
   }
}

 

This was selected as the best answer
D-VacD-Vac

Bob, thank you very much!  I will try this out.  

 

Really appreciate your help. 

 

I'm a big fan!

 
D-VacD-Vac

Yep.  This worked like a charm.  Thank you, Bob!