Top 5 opportunities for all users.

Question

I have to get the top 5 opportunities for all users.

One way to do this is:

for (User user: users) {
    SELECT ID, Amount FROM Opportunity where OwnerID = user.ID order by amount desc limit 5;
}

However with a lot of users this is going to run into governor limits. Any tips on how I gan get this info for all users in one query.

Answer 1

I would use SOQL with a Subquery. However, because there is no relationship syntax for the Opportunity OwnerId to User relationship (you'd expect it to be "Opportunities"), you'll first need to create another Lookup field on the Opportunity object to "mirror" the actual Owner. I'd call it OwnerMirror__c or OwnerDup__c, and I might even put a Lookup filter on it to restrict it to always be the Opportunity's actual Owner. Regardless, you'll need to create a Trigger on Opportunity which automatically sets this OwnerMirror__c to be whatever OwnerId is, at least on insert, and probably before update as well.

Example: a Visualforce page to display top 5 Opportunities for each User

// Apex controller
public with sharing class OppsController {
  public List<User> getOppsByUser() {
    return [
      SELECT Name,
        (select Name, Amount 
        from Opportunities__r
        order by Amount DESC
        limit 5)
      FROM User
      ORDER BY Name
    ];
  }
}  

Here's a sample Visualforce page:

<apex:page controller="OppsController" tabStyle="Opportunity">
<apex:pageBlock title="Top 5 Opportunities for each User">
<apex:repeat value="{!oppsByOwner}" var="owner">
    <apex:pageBlockSection title="{!owner.Name}">
    <apex:pageBlockTable value="{!owner.Opportunities__r}" var="opp">
        <apex:column value="{!opp.Name}"/>
        <apex:column value="{!opp.Amount}"/>
    </apex:pageBlockTable>
    </apex:pageBlockSection>
</apex:repeat>
</apex:pageBlock>    
</apex:page>

And here's the trigger code for before insert (very obvious but just thought I'd include it):

trigger Opportunity on Opportunity (before insert, before update) {
  // Set the OwnerDup field to the opp's OwnerId.
  // From here on out,
  // a Lookup Filter will force this field
  // to remain equal to the Opp's OwnerId
  for (Opportunity opp : Trigger.new) {
    if (Trigger.isInsert 
    || (Trigger.isUpdate && Trigger.oldMap.get(opp.Id).OwnerId != opp.OwnerId))
    opp.OwnerDup__c = opp.OwnerId;
  }
}

The Apex code could easily be used in an EmailHandler, Batch Apex, or other Apex context to persist or otherwise make use of this data.

Answer 2

You would need to 'bulkify' your query, instead of querying within the loop, try:

Set<ID> userIds = new Set<ID>();
for( User usr : users )
{
 userIds.add( usr.Id );
}

List<Opportunity> opps = [SELECT ID, Amount FROM Opportunity where OwnerID in userIds order by amount desc limit 5;

Of course, if your query returns too many rows, you will still hit a governor limit at which point you may have to consider an alternate approach depending on your use case.