You need to sign in to do that
Don't have an account?
Collection size 2,208 exceeds maximum size of 1,000.
I need to add a filter to the following code to mitigate the following error. Collection size 2,208 exceeds maximum size of 1,000
How do I add a filter to limit the SOQL call.
I am not a developer and do not have one to work on this issue as of yet, I have posted my code for this class and I need to be able to use all of the records, but do not know where to make the change to get past the 1000 limit. Any help would be appreciated.
//======================================================================
// Package: Opinion Leader Engagement System (OLES)
// Object: olesHomeController
// Company: The Coca-Cola Company
// Authors: John Westenhaver (Capgemini)& Ajaykumar Varakala (Capgemini)
// Comment: VF controller for home page. Implements search. It is a bit
// strange in that the search query pulls in two different
// queries and merges the lists into a third query because
// SF doesn't really support SQL. This page does NOT have a
// sort feature (thankfully).
//======================================================================
// Date Purpose
// Changes: 07/10/2010 Original version
//======================================================================
public with sharing class olesHomeController
{
public olesHomeController(ApexPages.StandardController controller) { }
public olesHomeController(ApexPages.StandardSetController controller) { }
private List<Contact> cList;
private List<Contact> cListOwner;
private List<Contact> cListFollower;
private String searchText;
Boolean hasSearched = false;
// Define the base SQL for this page.
// This page is a bit curious in that
public String sqlBase = 'SELECT Id, Name, LastName, FirstName, AccountId, Account.Name, Title, Location__c, Email, Phone, KeyFocusArea__c, Geographic_KO_Group__c, Sector__c, Geographic_Market__c, Picture__c FROM Contact ';
public String whereClause = ' ';
public String whereClauseOwner = ' WHERE (OwnerId = ' + '\'' + userInfo.getUserId() + '\') ';
public String whereClauseFollower = ' WHERE (OwnerId = ' + '\'' + userInfo.getUserId() + '\') ';
public String currentSql = sqlBase + whereClause + ' ORDER BY LastName, FirstName';
public String currentSqlOwner = sqlBase + whereClauseOwner;
public String currentSqlFollower = sqlBase + whereClauseFollower;
String cId = userInfo.getUserId();
private void showSql(String sql)
{
ApexPages.Message msg = new ApexPages.Message(ApexPages.Severity.INFO, sql);
ApexPages.AddMessage(msg);
}
// Execute the dynamic query.
public List<Contact> getContacts()
{
List<ID> cIds = new List<ID>();
String idString = ' ';
try
{
if ((cList == null) || (hasSearched))
{
// Get a list of contacts owned by the current user.
cListOwner = Database.query(currentSqlOwner);
// Get a list of contacts the current user is following.
cListFollower = Database.query(currentSqlFollower);
// Get lists of IDs from these queries.
IF (cListOwner.size() > 0)
{
for (Contact c : cListOwner)
{
cIds.add(c.Id);
}
}
IF (cListFollower.size() > 0)
{
for (Contact c : cListFollower)
{
cIds.add(c.Id);
}
}
// Make a string out of these contact IDs.
// Fortunately, any duplicates will be dropped automatically when we re-query this list.
if (cIds.size() > 0)
{
for (String id : cIds)
{
idString += '\'' + id + '\', ';
}
idString = idString.substring(0, (idString.Length() - 2));
whereClause = ' WHERE Id IN (' + idString + ') ';
}
// Assemble the final query string.
currentSql = sqlBase + whereClause + ' ORDER BY LastName, FirstName ';
// Get a list of these contacts in the correct order.
cList = Database.query(currentSql);
hasSearched = (cList.size() == 0);
}
return cList;
}
catch (QueryException qe)
{
apexPages.addMessages(qe);
return null;
}
}
// Return the current search string.
public String getSearchText()
{
return searchText;
}
// Set the current search string.
public void setSearchText(String searchText)
{
this.searchText = searchText;
}
// Assemble a new query string.
public PageReference search()
{
String userId = UserInfo.getUserId();
String qt = searchText + '%';
// Assemble owner where clause.
// Note the location has wildcards on both sides.
// whereClauseOwner = ' WHERE (OwnerId = ' + '\'' + userId + '\')' + ' AND ((FirstName LIKE ' + '\'' + qt + '\') ';
whereClauseOwner = 'WHERE ((FirstName LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (LastName LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Account.Name LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Title LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Location__c LIKE ' + '\'%' + qt + '\') ';
// whereClauseOwner += ' OR (KeyFocusAreas__c LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Geographic_KO_Group__c LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Sector__c LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' OR (Geographic_Market__c LIKE ' + '\'' + qt + '\') ';
whereClauseOwner += ' ) ';
// Assemble follower where clause.
// whereClauseFollower = ' WHERE (Id IN (SELECT au.Associated_Users__c FROM Associated_Users__c au WHERE au.Associated_KO__c = ' + '\'' + userId + '\')) ';
whereClauseFollower = ' WHERE ((FirstName LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (LastName LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Account.Name LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Title LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Location__c LIKE ' + '\'%' + qt + '\') ';
// whereClauseFollower += ' OR (KeyFocusAreas__c LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Geographic_KO_Group__c LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Sector__c LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' OR (Geographic_Market__c LIKE ' + '\'' + qt + '\') ';
whereClauseFollower += ' ) ';
// Assemble query strings.
currentSqlOwner = sqlBase + ' ' + whereClauseOwner + ' ORDER BY LastName, FirstName ';
currentSqlFollower = sqlBase + ' ' + whereClauseFollower + ' ORDER BY LastName, FirstName ';
// Force re-query.
cList = null;
cListOwner = null;
cListFollower = null;
hasSearched = true;
return null;
}
/*
apexpages.message msg = new apexpages.message(apexpages.severity.INFO,currentSql);
apexpages.addmessage(msg);
*/
static testMethod void testHomeController(){
List<Contact> conList = [Select Name From Contact Where OwnerId=:Userinfo.getUserId()];
olesHomeController HomeController = new olesHomeController(new ApexPages.StandardSetController(conList));
HomeController.getContacts();
HomeController.searchText = 'TestSearchText';
// HomeController.search();
PageReference result = HomeController.search();
Boolean b;
HomeController.hasSearched = true;
b = HomeController.hasSearched;
system.assertEquals(b,true);
}
Hey
You want to add the phrase "limit 1000" at the end of the SOQL query. This drops the amount that is retrieved down to 1000. Alternatively if you are going to try and use a collection greater that 1000 you could get a batch apex version of this written to do the processing on all the records. The key question is are you editing every record or just a subset (that is smaller than 1000)? If you are only using a <1000 subset then add an extra where filter to clean the search retrieval up some more.
Paul
Hello,
I've just posted a reply at the link below that should help you out.
http://boards.developerforce.com/t5/Visualforce-Development/Getting-around-1000-record-collection-size-limit-in/m-p/255905#M33279
HTH
Paul