+ Start a Discussion
angusgrantangusgrant 

Rewrite Apex class with only one SOQL statment not 26!

Hi I have written this apex class for a visualforce page which displays a list of Contacts sorted into Alpahabetical order displayed on a (pdf) page. I know that the way that I have written it will probably result in me hitting some of those governor limits. What I really need to do it write one SOQL statment that I then put into one list  that then gets sorted by Surname 1st character and seperated into 26 sepaerate lists. My Apex knowledge is not there yet can somone point me in the right direction? Thanks in advance.

 

current vf page and result:

 

 

current Apex class:



public class attendeecheckinlist2 {
string eventID = System.currentPageReference().getParameters().get('id');


public List<App__c> getApplicant_A() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'A%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_B() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'B%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_C() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'C%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_D() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'D%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_E() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'E%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_F() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'F%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_G() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'G%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_H() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'H%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_I() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'I%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_J() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'J%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_K() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'K%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_L() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'L%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_M() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'M%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_N() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'N%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_O() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'O%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_P() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'P%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_Q() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'Q%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_R() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'R%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_S() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'S%' Order by a.Applicant__r.Firstname ];
}

public List<App__c> getApplicant_T() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'T%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_U() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'U%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_V() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'V%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_W() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'W%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_X() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'X%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_Y() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'Y%' Order by a.Applicant__r.Firstname ];
}
public List<App__c> getApplicant_Z() {
return [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a Where a.SBS_Event__c = :eventID and a.Applicant__r.Lastname like 'Z%' Order by a.Applicant__r.Firstname ];
}

}

 

Best Answer chosen by Admin (Salesforce Developers) 
wesnoltewesnolte

Hey

 

Rather fetch all the records at once into a list and then get them out of that list when you need them e.g.

 

List<App__c> allApps = [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a];

 

and in each method e.g. the first one

 

 public List<App__c> getApplicant_A() {

   List<App__c> myApps = new List<App__c>();

   for(App_c app: allApps){

      if(app.SBS_Event__c = eventId && app.Applicant__r.lastname.startsWith('A'))

  myApps.add(app);

   }

    return myApps; 

   } 

 

An article on this sort of refactoring can be found here .

 

Cheers,

Wes 

All Answers

wesnoltewesnolte

Hey

 

Rather fetch all the records at once into a list and then get them out of that list when you need them e.g.

 

List<App__c> allApps = [Select a.Applicant__c, a.Applicant__r.FirstName, a.Applicant__r.LastName, a.Applicant__r.Salutation from App__c a];

 

and in each method e.g. the first one

 

 public List<App__c> getApplicant_A() {

   List<App__c> myApps = new List<App__c>();

   for(App_c app: allApps){

      if(app.SBS_Event__c = eventId && app.Applicant__r.lastname.startsWith('A'))

  myApps.add(app);

   }

    return myApps; 

   } 

 

An article on this sort of refactoring can be found here .

 

Cheers,

Wes 

This was selected as the best answer
angusgrantangusgrant
Hi Wes thanks for your prompt response to my query.  Will look at this latter this afternoon and update this thread with my findings.
angusgrantangusgrant

Hi Wes,

 

thanks your code worked perfectly  :smileyhappy:

 

thanks 

 

Angus