Showing posts with label Group By. Show all posts
Showing posts with label Group By. Show all posts

Sunday, March 19, 2017

Efficient coding using Group By Rollup

I have come across this use case recently during answering a question in Salesforce stackexchange.

Use Case

Family records are being captured as follows:


Requirement is to show repetitive summations of Weight and Workout_hours both at GrandParent and Parent level.

Expected result should look like this:

Solutions

I could think of preparing the data using GROUP BY ROLLUP.

SOQL will look like this:


SELECT GrandParent__c, Parent__c, Child__c, SUM(Weight__c), SUM(Workout_Hours__c) ,
GROUPING (GrandParent__c) grpGrantPt, GROUPING(Parent__c) grpPT
FROM Family__c
GROUP BY ROLLUP(GrandParent__c, Parent__c,Child__c)
ORDER BY GrandParent__c, Parent__c, Child__c

Query Result

The above query returns expected result, only thing I need to eliminate first record which is giving total summation. and two columns GROUPING (GrandParent__c) grpGrantPt, GROUPING(Parent__c) grpPT which are not needed to show in visualforce.



Controller
Now it is time to prepare the data so that it can be displayed into visualforce in a most efficient way. For this I have taken a help of Wrapper class (FamilyData)


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
public class FamilyClass
{
    public List<FamilyData> lstFamilyRecord {get;set;}

    public PageReference displayData()
    {
        Boolean firstRecord = true;

        lstFamilyRecord = new List<FamilyData>();
         AggregateResult[] lstFamily = [SELECT GrandParent__c, Parent__c, Child__c, SUM(Weight__c) weightTotal, SUM(Workout_Hours__c) whTotal ,
                                        GROUPING (GrandParent__c) grpGrantPt, GROUPING(Parent__c) grpPT
                                        FROM Family__c
                                        GROUP BY ROLLUP(GrandParent__c, Parent__c,Child__c)
                                        ORDER BY GrandParent__c, Parent__c, Child__c];

        for(AggregateResult familyObj:lstFamily)
        {
            if(!firstRecord)
            {
                FamilyData wrapper = new FamilyData();
                wrapper.GrantParent = (String) familyObj.get('GrandParent__c');
                wrapper.Parent = (String) familyObj.get('Parent__c');
                wrapper.Child = (String) familyObj.get('Child__c');

                if(familyObj.get('GrandParent__c') !=null && familyObj.get('Parent__c') !=null && familyObj.get('Child__c') !=null)
                {
                    wrapper.isChildRecord = true;
                }
                else if (familyObj.get('GrandParent__c') !=null && familyObj.get('Parent__c') !=null && familyObj.get('Child__c') ==null)
                {
                    wrapper.isParentRecord = true;
                }
                else if(familyObj.get('GrandParent__c') !=null && familyObj.get('Parent__c') ==null && familyObj.get('Child__c') ==null)
                {
                    wrapper.isGrantParentRecord = true;
                }
                wrapper.Weight = familyObj.get('weightTotal')!=null? ((Decimal)familyObj.get('weightTotal')).intValue():null;
                wrapper.WorkoutHours = familyObj.get('whTotal')!=null? ((Decimal)familyObj.get('whTotal')).intValue() :null;
                lstFamilyRecord.add(wrapper);
            }
            firstRecord = false;
        }

        return null;
    }

    public class FamilyData
    {
        public String GrantParent {get;set;}
        public String Parent {get;set;}
        public String Child {get;set;}
        public Integer Weight {get;set;}
        public Integer WorkoutHours {get;set;}
        public Boolean isGrantParentRecord {get;set;}
        public Boolean isParentRecord {get;set;}
        public Boolean isChildRecord {get;set;}
    }

}

Visualforce



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<apex:page id="familyPage" controller="FamilyClass" action="{!displayData}" showheader="true" sidebar="false"> 
    <table id="familytable" border="1"> 
        <tr>
            <th>Parents</th>
            <th>Weight</th>
            <th>Work Hours</th>
        </tr>             
        <apex:repeat id="myRepeat" value="{!lstFamilyRecord}" var="key"> 
            <tr>
                <td>
                    <apex:outputText style="color: red;" value="{!key.GrantParent}" rendered="{!key.isGrantParentRecord}"/>
                    <apex:outputText style="color: blue;" value="{!key.Parent}" rendered="{!key.isParentRecord}"/>
                    <apex:outputText value="{!key.Child}" rendered="{!key.isChildRecord}"/>
                </td>
                <td>
                    <apex:outputText value="{!key.Weight}"/>
                </td>
                <td>
                    <apex:outputText value="{!key.WorkoutHours}"/>
                </td>
            </tr>                       
        </apex:repeat>
    </table>          
</apex:page>

Final Output

Conclusion

Usually, looking at the requirements we think of preparing the data and logic in apex or visualforce in a complex way. But if we can leverage SOQL logic efficiently like this we may end up nice implementation.

Sharing with you, if it helps!

Link to stackExchange question:
http://salesforce.stackexchange.com/questions/164382/how-to-iterate-repetitive-rows-in-table-to-calculate-count-and-show-the-sum/164394#164394

Posted by at 2 comments:
Labels: , ,
Subscribe to: Posts (Atom)