I have a pageblock table with the following columns:

Company, Package, Type, Revenue, Age, Users, Total Usage Actions, Top 3 Modules.

All of the fields apart from "Total usage actions" and "Top 3 modules" can be obtained directly in a select statement.

Total usage actions requires a subquery:

SELECT Action__c FROM Usage__r

And returns me a list of Usage__r object ids which I am getting the size in the page block Table column using {!c.Usage__r.size}

I am using a StandardSetConnector to enable me to have pagination.

Each usage object is a child of a company and it is the field Action__C that contains the actual action that was performed in our app.

I wish to get the 3 most popular actions per company and display this in the Top 3 modules column, however I am unsure of how I would even calculate this. I suspect I may have to perform a separate query per company something similar to this:

SELECT Action__c WHERE Company__c = companyid 
                 GROUP BY Company__c, Action__c order by SUM(Action__c)
  • What is a most popular action? Greatest values? – Sergej Utko Jun 30 '16 at 11:44
  • The most popular action is the one that occurs the most often. e.g. Usage objects that have Action__c field "dashboard", i want to sum the actions fields. – GJOkoro Jun 30 '16 at 15:29

Actually you already have all information you need. It located within your Usage__r collection. All you need to do is to sort the list by Action__c field. Then use the apex:repeat to iterate this collection in each "Top 3 Module" column and limit the number of rows to 3. So you will get the very first three values of this collection (already sorted by ORDER BY Action__c):

The query:

List<Company__c> c = [Select Id, Name, Package__c, Type__c, Revenue__c, Age__c, Users__c,
                            (Select Action__c 
                             From Usage__r
                             Order By Action__c DESC)
                      From Company__c];

Visualforce page:

<apex:pageBlockTable value="{!c}" var="company">
    <apex:column value="{!company.Name}">
    <apex:column headerValue="Total usage actions" value="{!company.Usage__r.size}"/>
    <!-- Top 3 action -->
    <apex:column headerValue="Top 3 Modules">
        <apex:repeat value="{!company.Usage__r}" var="{!usage}" rows="3">
            <apex:outputText value="{!usage.Action__c}" /> <br/>
  • Hi Sergey thanks for the reply, however am I correct in understanding that the subquery will return 3 actions sorted alphabetically, instead of 3 Actions sorted by the count of the occurrences of each action? – GJOkoro Jun 30 '16 at 13:26
  • Also the subquery is returning a List of Usage id Strings, despite selecting only the Action__c field, how can i select this field? – GJOkoro Jun 30 '16 at 13:56
  • Well, based on your requirement about most "popular" Action I think you will have to write a wrapper class for the list of values displayed on the page. And you will need to build an extra SOQL query to get the most popular actions using aggregate function(s) because the aggregate functions are not working on the subquery :( – Sergej Utko Jun 30 '16 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.