1

I am trying to select a list of Opportunities from SFDC Partner API using an authenticated SessionId etc. using SOQL, The query looks like this:

SELECT 
    Name, ExpectedRevenue, StageName, CloseDate, OwnerId, Id, 
    (SELECT activitydate, Id FROM Tasks WHERE activitydate > today ORDER BY activitydate ASC LIMIT 1), 
    CreatedDate, IsClosed, AccountId, Probability, Value__c, Power__c,
    Plan__c, Num_Letters__c, Num_Messages__c, Num_eVPs__c, Associated_Organisation_Chart__c, Flagged__c, 
    Qualified_Prospect_Formula__c 
FROM 
    Opportunity 
ORDER BY 
    tasks[0].ActivityDate Desc 
NULLS last 
LIMIT 20 
OFFSET 0

I realise the

ORDER BY tasks[0].ActivityDate

will cause problems, but hopefully you can see what I am trying to achieve, which is to return pages of Opportunities, ordered by the opportunity's next task activity date to occur after "today".

I am introducing paging to this to speed up the load times of our application. Previously it was selecting 1000 opportunities, and paging client side, now it selects 20 and pages server side, so this SOQL problem wasnt an issue, until now.

Because I am doing all of this to make speed enhancements, I need to do it in 1 query with offset/limit etc. included.

If it is simply not possible, is there another "fast" way of achieving this? I started to look into calling APEX webservice methods, but I'd much prefer to just adjust my existing query, which is called via the Partner API.

1 Answer 1

0

Mike, there are two issues here. The first one is the sort and the second the pagination.

I am not sure what you're trying to do (I'd need to see controller/page for that) but usually, to paginate, we try not to use offset. OFFSET only works up to 2000 records, and that is a limit most implementations cannot accept. Instead, you want to use a StandardSetController. The other advantage is that you can construct your query dynamically, so you can inject any string into the "ORDER BY" clause.

One way to use the StandardSetController is this:

To Initialize

query = 'your SoQL query here, as a string';
ApexPages.StandardSetController setCon = new ApexPages.StandardSetController(Database.getQueryLocator(query)); 
setCon.setPageSize(20); //The Page Size

To get the records for a given page

(List<Opportunity>) setCon.getRecords();

To move among pages, or to go to a specific page

setCon.last();
setCon.first();
setCon.previous();
setCon.next;
setCon.setpageNumber(pageNumber);

You can get the whole documentation here and here.

=== UPDATE ==

After reading your question twice, I understand the sorting issue better. I am afraid you cannot sort the opportunities in the desired way. I would build a batch Job/trigger (or maybe Process Builder, depending on the complexity), to save the "Next After Today" date in the Opportunities and then sort by that field.

1
  • thanks - i wondered if triggers would be another route. I will look into that tomorrow - thanks! Commented Mar 23, 2017 at 19:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .