You need to sign in to do that
Don't have an account?
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 ];
}
}
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
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
Hi Wes,
thanks your code worked perfectly
thanks
Angus