Apex Code Development You need to sign in to do that
Don't have an account?
tsalb SOQL Order By - conditional sorting
I tried using CASE statements and it doesn't look like SOQL accepts it? I need to sort by 5 specific records being on top IF the vendor type is of a certain selection.
I have a SOQL statement here:
try {
Set<Id> radiusSet = radiusMap().keySet();
vendors = (List<Contact>)[SELECT Id, Name, Phone, Email, Primary_County__c, Type__c, Status__c, Overall_Rating__c, RecordTypeId, Designation__c
FROM Contact
WHERE Primary_County__c IN :radiusSet
AND Type__c INCLUDES (:vType)
AND (Primary_Specialty__c LIKE :noSpecialty OR Primary_Specialty__c LIKE :specialty)//Used to return Vendors with or without Primay Specialties
ORDER BY Overall_Rating__c DESC];
}
catch(QueryException q) {
}
return vendors;
}I'm trying to modify like this...something like this at least...split the statement depending on the vendorType picklist - then adding sorts specific to if the type == commercial appraiser.
How can i get it to sort for specific results every single time I have commercial appraiser chosen? AND have it ignore(?) the radius WHERE statement?
List<Contact> vendors;
if(vType!= 'Commercial Appraiser'){
try {
Set<Id> radiusSet = radiusMap().keySet();
vendors = (List<Contact>)[SELECT Id, Name, Phone, Email, Primary_County__c, Type__c, Status__c, Overall_Rating__c, RecordTypeId, Designation__c
FROM Contact
WHERE Primary_County__c IN :radiusSet
AND Type__c INCLUDES (:vType)
AND (Primary_Specialty__c LIKE :noSpecialty OR Primary_Specialty__c LIKE :specialty)
ORDER BY Overall_Rating__c DESC];
}
catch(QueryException q) {
}
return vendors;
}
else if(vType == 'Commercial Appraiser'){
try {
Set<Id> radiusSet = radiusMap().keySet();
vendors = (List<Contact>)[SELECT Id, Name, Phone, Email, Primary_County__c, Type__c, Status__c, Overall_Rating__c, RecordTypeId, Designation__c
FROM Contact
WHERE Primary_County__c IN :radiusSet
AND Type__c INCLUDES (:vType)
AND (Primary_Specialty__c LIKE :noSpecialty OR Primary_Specialty__c LIKE :specialty)
AND (Id = '003A000000PvSHQ')
ORDER BY Id = '003A000000PvSHQ', Overall_Rating__c DESC];
}
catch(QueryException q) {
}
return vendors;
}
}
Dirty workaround that uses the SOQL as the modifier...but it works and didn't take too long to think up. Maybe this will help as an alternative solution for some?
vendors = (List<Contact>)[SELECT Id, Name, Account.Name, Account.Id, Phone, Email, Primary_County__c, MailingPostalCode, Type__c, Status__c, Status_Sort__c, Top__c, Average_Vendor_Overall_Rating__c, Average_of_Vendor_Future_Use__c, RecordTypeId FROM Contact WHERE (Primary_County__c IN :radiusSet) OR (Top__c = true)) AND Type__c INCLUDES (:vType) AND (Primary_Specialty__c LIKE :noSpecialty OR Primary_Specialty__c LIKE :specialty) ORDER BY Top__c DESC, Status_Sort__c ASC, Average_of_Vendor_Future_Use__c DESC];All Answers
ORDER BY Id = '003A000000PvSHQ' i don't think this will workfor you rather you should get a list from your SOQL and then in apex you can prepare another list as per you business logic in which those 5 should come on the top. and use that list in you VFP, if you want to show them.
please let me know if any isuue in it.
Hi,
First you need to fetch all the data then after fetching the data you can apply your business logic to that and generate a new list and use that in your UI. That is the best aproach generally people follow.
regards,
Aditya.
Would either of you have any examples (online articles or anything) that highlight the methods you describe? I would appreciate it if you could link me to something, Thanks!
Anyone have any articles they could forward me?
Hi ,
I have written some code in my project which is for a search functionality where i am generating the SOQL query based on the search field that the user has entered.
So similar way you can also in your project generate a SOQL query dynamically and update the list. Below is the sample code which i have written in my sample.
public newPaginationSampleController(){
records=10;
startNo=0;
endNo=startNo+records;
queryString='Select name,First_Name__c,Last_Name__c,Country__c,Action__c From SampleObject001__c where First_Name__c!=null';
userDetails=executeQuery();
count=userDetails.size();
if(endNo >= count){
endNO = count;
}
}
public List<SampleObject001__c> getUserDetails(){
try{
List<SampleObject001__c> usrDetails=new List<SampleObject001__c>();
system.debug('-----------userDetails---------'+userDetails.size());
for(Integer i=startNo ; i<endNo ; i++){
usrDetails.add(userDetails.get(i));
}
return usrDetails;
}catch(Exception E) {
return userDetails;
}
}
public void sortFunction() {
sortDirection = sortDirection.equals('asc') ? 'desc' : 'asc';
//searchFunctionality();
userDetails=executeQuery();
}
public List<SampleObject001__c> executeQuery(){
try{
List<SampleObject001__c> usrdetails=Database.query(queryString+' order by '+sortField+' '+sortDirection+' limit 200');
return usrdetails;
}catch(Exception e){
}
return null;
}
public PageReference searchFunctionality(){
String firstName = Apexpages.currentPage().getParameters().get('firstname');
String lastName = Apexpages.currentPage().getParameters().get('lastname');
String country = Apexpages.currentPage().getParameters().get('country');
queryString='Select name,First_Name__c,Last_Name__c,Country__c,Action__c From SampleObject001__c where First_Name__c!=null';
if(firstName!=null && !firstName.equals('')){
queryString+=' and First_Name__c LIKE \''+String.escapeSingleQuotes(firstName)+'%\'';
}
if(lastName!=null && !lastName.equals('')){
queryString+=' and Last_Name__c LIKE \''+String.escapeSingleQuotes(lastName)+'%\'';
}
if(country!=null && !country.equals('')){
queryString += ' and Country__c LIKE \''+country+'%\'';
}
userDetails=executeQuery();
records=10;
startNo=0;
endNo=startNo+records;
count=userDetails.size();
if(endNo >= count){
endNO = count;
}
return null;
}
But make sure that in your page you need to mention based on which field you need to fetch the data and in the controller conserned properties should be there.
If you have any queries let me know.
Regards,
Aditya.
Dirty workaround that uses the SOQL as the modifier...but it works and didn't take too long to think up. Maybe this will help as an alternative solution for some?
vendors = (List<Contact>)[SELECT Id, Name, Account.Name, Account.Id, Phone, Email, Primary_County__c, MailingPostalCode, Type__c, Status__c, Status_Sort__c, Top__c, Average_Vendor_Overall_Rating__c, Average_of_Vendor_Future_Use__c, RecordTypeId FROM Contact WHERE (Primary_County__c IN :radiusSet) OR (Top__c = true)) AND Type__c INCLUDES (:vType) AND (Primary_Specialty__c LIKE :noSpecialty OR Primary_Specialty__c LIKE :specialty) ORDER BY Top__c DESC, Status_Sort__c ASC, Average_of_Vendor_Future_Use__c DESC];