Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.

share|improve this question
    
What is the context for this request? Where will you be displaying this information? –  zachelrath Dec 21 '12 at 11:53
    
@zachelrath I am persisting it and then sending it in an email. –  dublintech Dec 21 '12 at 12:15
    
Ah, just posted an example for Visualforce --- but you can easily use the same procedure for persisting and sending in an email. –  zachelrath Dec 21 '12 at 12:40
add comment

2 Answers 2

up vote 3 down vote accepted

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.

duplicate Owner lookup field on Opportunity

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>

Visualforce Page showing top 5 opportunities by Owner

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.

share|improve this answer
    
Not sure this will work for me but it is as close as I'll get to an answer. –  dublintech Jan 31 '13 at 9:35
add comment

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.

share|improve this answer
    
I need top 5 opportunities for every user. So if I have 100 users, that's 500 Opportunities I need. –  dublintech Dec 21 '12 at 11:39
    
I think you would need to retrieve all records and use Apex to, i'll modify the answer to reflect what i mean. –  Phil Hawthorn Dec 21 '12 at 12:35
    
Actually, I won't - Zach's sub-query looks like it will give you what you need without any additional Apex to group etc. –  Phil Hawthorn Dec 21 '12 at 12:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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