Find your content:

Search form

You are here

Homeยป

Apply List Views to large pagination sets

 
Share

I want to apply a list view to a standard set controller (only way to apply is a list view is the standard set controller)

If the my query returns the maximum number of rows in a SOQL query: 10000 rows, and the maximum pagesize for each set of results is: 2000 (standard set controller limit)

I can paginate over 2000 records at a time, but the order isn't guaranteed to be the same after the list view is applied (list views apply their own sort order).

My question is, if I give the standard set controller a query with an order by clause, and increment the page number for each set of 2000 records (page limit size), than apply the list-view, is it guaranteed that I would get all the records if I kept incrementing the offset? Order does the list view filter change the order of the results? 

string SOQL = 'SELECT Id,StartDate FROM Campaign WHERE StartDate != null ORDER BY StartDate ASC LIMIT 10000';
ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(Database.getquerylocator(SOQL)); 
ssc.setPageSize(2000);
ssc.setFilterId(filterId);
ssc.setPageNumber(1);

Attribution to: jordan.baucke

Possible Suggestion/Solution #1

I've actually suspected for a while that the WHERE or ORDER BY clauses given to the StandardSetController really don't make much difference once you have called setFilterId.

I wrote this small test and grabbed the debug log (sadly the internal SOQL made by the setFilterId does not show). However it does prove the ORDER BY gets wiped, though the selected fields do not. Hope this helps clear a few things up!

string SOQL = 'SELECT Id, Name, Phone FROM Account Order By Name DESC';
List<Account> accounts = Database.query(SOQL);
for(Account account : accounts)
    System.debug(account.Name + ', Phone: ' + account.Phone);
ApexPages.StandardSetController ssc = 
     new ApexPages.StandardSetController(Database.getquerylocator(SOQL)); 
ssc.setPageSize(2000);
ssc.setFilterId('00BG000000702tj'); // 'Platinum and Gold SLA Customers'
ssc.setPageNumber(1);
for(Account account : (List<Account>) ssc.getRecords())
    System.debug(account.Name + ', Phone: ' + account.Phone);

This is the debug output...

24.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
Execute Anonymous: string SOQL = 'SELECT Id, Name, Phone FROM Account Order By Name DESC';
Execute Anonymous: List<Account> accounts = Database.query(SOQL);
Execute Anonymous: for(Account account : accounts)
Execute Anonymous:  System.debug(account.Name + ', Phone: ' + account.Phone);
Execute Anonymous: ApexPages.StandardSetController ssc = new     ApexPages.StandardSetController(Database.getquerylocator(SOQL)); 
Execute Anonymous: ssc.setPageSize(2000);
Execute Anonymous: ssc.setFilterId('00BG000000702tj'); // 'Platinum and Gold SLA Customers'
Execute Anonymous: ssc.setPageNumber(1);
Execute Anonymous: for(Account account : (List<Account>) ssc.getRecords())
Execute Anonymous:  System.debug(account.Name + ', Phone: ' + account.Phone);
11:02:43.069 (69712000)|EXECUTION_STARTED
11:02:43.069 (69726000)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
11:02:43.071 (71059000)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|SELECT Id, Name, Phone FROM Account Order By Name DESC
11:02:43.074 (74502000)|SOQL_EXECUTE_END|[2]|Rows:12
11:02:43.075 (75232000)|USER_DEBUG|[4]|DEBUG|University of Arizona, Phone: (520) 773-9050
11:02:43.075 (75315000)|USER_DEBUG|[4]|DEBUG|United Oil & Gas, UK, Phone: +44 191 4956203
11:02:43.075 (75384000)|USER_DEBUG|[4]|DEBUG|United Oil & Gas, Singapore, Phone: (650) 450-8810
11:02:43.075 (75451000)|USER_DEBUG|[4]|DEBUG|United Oil & Gas Corp., Phone: (212) 842-5500
11:02:43.075 (75517000)|USER_DEBUG|[4]|DEBUG|sForce, Phone: (415) 901-7000
11:02:43.075 (75584000)|USER_DEBUG|[4]|DEBUG|Pyramid Construction Inc., Phone: (014) 427-    4427
11:02:43.075 (75650000)|USER_DEBUG|[4]|DEBUG|Grand Hotels & Resorts Ltd, Phone: (312) 596-1000
11:02:43.075 (75715000)|USER_DEBUG|[4]|DEBUG|GenePoint, Phone: (650) 867-3450
11:02:43.075 (75780000)|USER_DEBUG|[4]|DEBUG|Express Logistics and Transport, Phone: (503) 421-7800
11:02:43.075 (75846000)|USER_DEBUG|[4]|DEBUG|Edge Communications, Phone: (512) 757-6000
11:02:43.075 (75911000)|USER_DEBUG|[4]|DEBUG|Dickenson plc, Phone: (785) 241-6200
11:02:43.075 (75984000)|USER_DEBUG|[4]|DEBUG|Burlington Textiles Corp of America, Phone: (336) 222-7000
11:02:43.076 (76365000)|SOQL_EXECUTE_BEGIN|[5]|Aggregations:0|SELECT Id, Name, Phone FROM Account 
11:02:43.124 (124106000)|SOQL_EXECUTE_END|[5]|Rows:12
11:02:43.164 (164716000)|USER_DEBUG|[10]|DEBUG|Burlington Textiles Corp of America, Phone: (336) 222-7000
11:02:43.164 (164798000)|USER_DEBUG|[10]|DEBUG|Dickenson plc, Phone: (785) 241-6200
11:02:43.164 (164866000)|USER_DEBUG|[10]|DEBUG|Edge Communications, Phone: (512) 757-6000
11:02:43.164 (164932000)|USER_DEBUG|[10]|DEBUG|Express Logistics and Transport, Phone: (503) 421-7800
11:02:43.165 (165011000)|USER_DEBUG|[10]|DEBUG|GenePoint, Phone: (650) 867-3450
11:02:43.165 (165079000)|USER_DEBUG|[10]|DEBUG|Grand Hotels & Resorts Ltd, Phone: (312) 596-1000
11:02:43.165 (165145000)|USER_DEBUG|[10]|DEBUG|Pyramid Construction Inc., Phone: (014) 427-4427
11:02:43.165 (165210000)|USER_DEBUG|[10]|DEBUG|sForce, Phone: (415) 901-7000
11:02:43.165 (165281000)|USER_DEBUG|[10]|DEBUG|United Oil & Gas Corp., Phone: (212) 842-5500
11:02:43.165 (165347000)|USER_DEBUG|[10]|DEBUG|United Oil & Gas, Singapore, Phone: (650) 450-8810
11:02:43.165 (165413000)|USER_DEBUG|[10]|DEBUG|United Oil & Gas, UK, Phone: +44 191 4956203
11:02:43.165 (165478000)|USER_DEBUG|[10]|DEBUG|University of Arizona, Phone: (520) 773-9050
11:02:43.289 (165570000)|CUMULATIVE_LIMIT_USAGE
11:02:43.289|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 2 out of 100
  Number of query rows: 36 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Number of script statements: 30 out of 200000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 10
  Number of Email Invocations: 0 out of 10
  Number of fields describes: 0 out of 100
  Number of record type describes: 0 out of 100
  Number of child relationships describes: 0 out of 100
  Number of picklist describes: 0 out of 100
  Number of future calls: 0 out of 10

Attribution to: Andrew Fawcett

Possible Suggestion/Solution #2

Offset keyword you cant have greater than 2000 and so you cannot increment that.There will be exception thrown.

Refer this doc

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_offset.htm

This is the error you will hit 'NUMBER_OUTSIDE_VALID_RANGE '


Attribution to: Mohith Shrivastava
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/3390

My Block Status

My Block Content