+ Start a Discussion
AshlekhAshlekh 

Custom Roll Up - Need Count and Avg.

Hi All,

I need a small suggestion from your side for below scenario.

I've two objects X and Y both are in Look-up relationship. X(Parent) and Y(Child).

I need to calcualte the total childs (y records for a particular x record and on Y object there is field call price and I need to calcualte average of price and save on X (parent) record.

This is a like custom Roll up fields so we need to write a trigger to handle all cases like insert, update delete and undelete.

But my question is which approach we need to take to accomplish this.

1 Approch ) Fire a Aggregate query on Y records and group by X record in the trigger of Y object on above mentioned events. ( Put where condition to get only those parent X recs which are present in y object records in trigger)
2 Approch) Get all those X record which are present in list of y records in trigger and then do +1 or -1 to current present value of x count field and and save.

Which approch is good and why. Please keep in my that data may be in bulk.

-Thanks
Ashlekh Gera
Nayana KNayana K
Let's say we have 2 parents (p1, p2, p3) and p1 has 30children and p2 has 50 children and p3 has 0 children.

Approach1 : Aggregate query:

setParentId = {p1,p2, p3};
Here we will query something like this :
SELECT ParentId, COUNT(Id) 
FROM Child 
WHERE ParentId IN: setParentId
GROUP BY ParentId

Now the result will be :
ParentId: p1 COUNT: 30
ParentId: p2 COUNT: 50

Here you cannot get ParentId: p3 COUNT: 0 because you are query is on child so if no child is present it is not going to give the rollup count 0.
But if you observe, the records returned is of size 2.
Now, if we want COUNT with children + COUNT 0 for without children using aggregate query. Then use below approach.
Set<Id> setParentId = {p1,p2, p3};
Set<Id> setWith0Children = new Set<Id>(setParentId);
Map<Id, Integer> mapPIdToCount = new Map<Id, Integer>();

for(AggregateResult ar : [SELECT ParentId, COUNT(Id)  cnt
					FROM Child 
					WHERE ParentId IN: setParentId
					GROUP BY ParentId])
{
	// count for the parents with children
	mapPIdToCount.put(ar.ParentId, ar.cnt);
}

setWith0Children.removeAll(mapPIdToCount.keySet());

//Now, you have parent Ids without children
for(Id idP : setWith0Children)
{
	mapPIdToCount.put(idP, 0);
}

Here we have 2 for loops but I think it gives faster results.

Approach2 : Sub query:
Approach1 : Sub query:

Set<Id> setParentId = {p1,p2, p3};
Map<Id, Integer> mapPIdToCount = new Map<Id, Integer>();

for(Parent p : [SELECT Id, (SELECT Id FROM Children) 
					WHERE Id IN: setParentId
					])
{
	if(!p.isEmpty())
	{
		// count for the parents without children
		mapPIdToCount.put(p.Id, 0);
	}
	else
	{
		
		// count for the parents with children
		mapPIdToCount.put(p.Id, p.Children.size());
	}
}

Here the query returns 3 parent records + (30+50 = 70) 70 children = 73 records returned from the query, just to calculate a count.

I strongly recommend aggregate query but it depends on your requirement. 

Do one thing. Use both the approach one by one and by using Limit.getLimitCpuTime check how much time it takes for the same set of records.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_limits.htm

 
AshlekhAshlekh

Hi Nayana,

Thanks for your response on this.

In Approach1 : Aggregate query: Total Rows will be 50(p1 childs) + 30 (P2 Childs) + 0 (P3 childs) = 80 rows. (Gov. limit is 50K) 
In Approach2 : Sub query: Total Rows will be 50 + 30 +3 = 80 rows. (Gov. limit is 50K).

Postive Point = Above approaches are good to maintain the Count Value on Parent record because if someone change the value directly on Parent record than trigger will correct next time when child record get insert or updated.

Negitive Point = It may hit the Governor Limit of 50K.
If I upload multiple record of childs with different Parents and those parents has many childs recods and it will take more rows and time to update.


 
I am also aware of these things but yes I want to discuss on this matter.

What I am thinking is to take a another approch.

Approach 3 : P1 ,P2 and P3 are Parent and don't have childs. And there is a field on records call count and initial it is set to 0;

Now some has uploaded 100 records of childs for P1 and 150 for P2 and 50 for P3.

INSERT - Trigger --> Will make a set of Parent Records - set contains (P1,P2,P3) and query on P Object to get these records to know the current count value and hold this record in LIST ParentLIST ( 3 rowsquery from 50K)
And furture we will make a Map of P records Id against list of Childs to maintain single parent have how many childs on current new records. 

ex: MAP<ID(p id's),List<Child(childrecord)>>.

By this map the result will be. 
Map<P1,100Childs>
Map<P2,150childs>
Map<P3,50childs> 

Now we have a list ParentLIST we can find out the P1 record from the list for current count and also take the no of childs record from map and add the current count + size of childs in map for P1. 
Same for P2 and P3.

And Update the records of P object.

In this case I've only use 3 QueryRows from 50K.

Positive Point - It will not use more 50K Gov. Limit.  Not more interation with database to get data and do the calculation in code.

Negitive Point - If someone manaually change the Count value on Parent record than count will be always wrong. 
(We can stop user to change manually by trigger)

What do you think on  this approach?

-Thanks
Ashlekh Gera



  


 

Nayana KNayana K
In Approach1 : Aggregate query: Total Rows will be 50(p1 childs) + 30 (P2 Childs) + 0 (P3 childs) = 80 rows. (Gov. limit is 50K) .

No, here you will get Total Rows = 2. That is why it is useful.
 
AshlekhAshlekh
Here is the example...

User-added image
In the screen shot you can see I just he aggregate query for a single record ( want to calculate the no. of child for a P1 record)

Result showing that query rows was 2 becuase there was 2 child found for P1 record.

Hence proved.

Thanks
Ashlekh Gera
Nayana KNayana K
User-added image
Actually it is returning 1 row (black box). I have tried aggregate query in anonymous window of personal orga and in both the places (red box and black box), I got same number of rows and it is not the total child rows, it is just 2 as I have conveyed already.

Not sure what's happening in your case.
AshlekhAshlekh
Hi,

Salesforce Statement - 

Queries that include aggregate functions are subject to the same governor limits as other SOQL queries for the total number of records returned. This limit includes any records included in the aggregation, not just the number of rows returned by the query. If you encounter this limit, you should add a condition to the WHERE clause to reduce the amount of records processed by the query.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

Btw - thanks for this conversation Nayana.

-Thanks
Ashlekh Gera
Nayana KNayana K
Thanks Ashlekh,

I was wrong! Have to revise basics again :)