You need to sign in to do that
Don't have an account?
Ashlekh
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
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
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.
Approach2 : Sub query:
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
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
No, here you will get Total Rows = 2. That is why it is useful.
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
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.
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
I was wrong! Have to revise basics again :)