Trigger to count most frequent recurring value in a related list?

Question

I'm wondering whether its possible to use a trigger to find the most frequently recurring value in a related list, and then populate a field on the parent object with that value. And if so, what trigger structure might best achieve this?

for example, the list of values in the custom field of related list records might be:

list relatedList = dog, cat, bird, bird, bird, cat, chicken, dog, horse, bird, dog, bird

I want the trigger to iterate through this list, and find the most frequently recurring value. So.... 'dog' occurs 3 times 'cat' occurs 2 times 'bird' occurs 5 times 'chicken' occurs 1 times 'horse' occurs 1 times

So.....'bird' occurs most frequently, therefore I want to populate the parent object field 'frequent_item__c' with 'bird'

I'm thinking the trigger structure might be something like...

//iterate through the list
//for each item in the list, count the number of identical items
//find the item with the highest frequency count
//populate the parent object field with the item

Am I on the right track?? I'm fairly new to Apex development with a few triggers and test classes under my belt, but feel that this one is a bit out of reach. Any assistance appreciated.

Thanks,

Answer 1

This could get a bit sketch if the numbers of records are large, but if they're managable within limits then some aggregate SOQL could be of use here.

For example to get all the counts for the various related records (I assume you're grouping them by some kind of field, like Type__c:

select count(Id) from Child__c where Parent__c = : someId group by <<field to count>>

This gets more tricky when dealing with multiple parents, but you can group by those too:

select count(Id), Parent__c from Child__c where Parent__c in : <<setOfParents>> group by Parent__c, <<field to count>>

Then it'll just be a case of looping through your results and finding the highest number of children for each parent, something like:

Map<String, Integer> parentToHighest = new Map<String, Integer>();
Map<String, String> parentToValue = new Map<String, String>();

for(AggregateResult ar : [ select count(Id) num, Type__c, Parent__c from Child__c where Parent__c in : trigger.new.KeySet() group by Parent__c, Type__c])
{
    Integer num = (Integer)ar.get('num');
    String name = (String)ar.get('Parent__c');
    String type = (String)ar.get('Type__c');

    if(parentToHighest.get(name) == null || parentToHighest.get(name) < num)
    {
        parentToHighest.put(name, num);
        parentToValue.put(name, type);
    }
}

Now, if you need it you'll have a map of parent id -> count of highest child, and also a map of parent -> type of child which you can use to populate the appropriate parent field.

You'll need to work out what you want to do when two children types have the same count!