Find the max owner for a given record

Question

Schema

I have a scenario where two records are related via Lookup.

A -> B(has Lookup[A])

• So parent A can have many related children B objects.

• Since A and B are related through lookup and not through Master-Detail, Owner of A will be different from Owner of related children B objects

Problem:

I want to find the maximum count of owners for B with given parent A and assign it to parent A object. Please provide me an approach using SOQL(recommended) or Apex.

My Try

1. Took SOQL result of B's Owner with A.Id

   [Select A.Id,B.OwnerId From B]

2. Formed a Map with key as A.Id and value as OwnerIds

   Map<Id,List<Id>> AToOwner = new Map<Id,List<Id>>()

3. Convert the List to occurrence hash

   {U1,U1,U2} => { U1 => 2, U2 => 1}

4. Find the Max occurence Owner and set the Owner for parent A

   A.OwnerId = U1 // Since U1 has max occurence of 2

   Currently it works fine for smaller record set of size 100 but fails with CPU limit exceeded for larger size.

Test Input Data

A  | B | B's Owner
----------------
1   X   U1
1   Y   U1
1   Z   U2
2   A   U3 

Expected Output result

A  | OwnerId | Count as B's Owner
------------------------------
1     U1          2
2     U3          1

Answer 1

You should better use aggregate functions for grouping fields. With following query you should get exactly what you need. First group the SOQL query by object-A-ID and owner-ID. You will get then the following results:

A-ID  | Count as B's Owner | OwnerId
------------------------------------
1       15                   U1
2       7                    U2

Then iterate through the results. For each iteration create an temp object-A, assign it ID and a new ownerID and add each one to the list of objects A. Then update the list:

List<objectA__c> objectsA = new List<objectA__c>();
for(AggregateResult ar : [select count(id), objectA__c, ownerid
                          from objectB__c
                          group by objectA__c, ownerid]){

    objectA__c obj = new objectA__c(id = ar.get('objectA__c'), ownerid = ar.get('ownerid'));
    objectsA.add(obj);
}

update objectsA;

I haven't tested the code, use it as template for your implementation.