How to sort Contact record descending based on the Proficiency and ascending based on the Skill type

Question

Can someone assist me on how can I get the contact record sorted (Descending -> Proficiency) and (Ascending -> Skill Type) based on my query below.

Employee Skills, Training and Certifications has a lookup relationship with Contact (Employee).

SELECT Name, Email
, (SELECT Skill_ID__r.Name, Proficiency__c, Year_of_Experience__c, Skill_Type__c FROM Employee_Skills__r)
, (SELECT Training_ID__r.Name FROM Employee_Training__r)
, (SELECT Certification_ID_r.Name FROM Employee_Certifications__r)
FROM Contact

Answer 1

You can't sort the parent object based on the results of a subquery. You can sort the objects in the subquery but that only affects the child objects, not the parent object.

Sorting like that doesn't really make sense in the context of parent-child relationships, though. Since you could have multiple Employe_Skill__c records per contact there's not really a way for the query to know which one to sort the contact record on.

Answer 2

According to your requirement, you need to divide the SOQL into 2 parts.

1) Retrieve the sort list of Employee_Skills__c records and put those into wrapper class.

2) Retrieve the Contact records with Employee_Training__r and Employee_Certifications__r.

3) Finally compare the contact Ids and assign the values.

public class WrapperClass
{
    public Id contactId{get;set;}
    public String ContactName {get;set;}
    public String SkillName{get;set;}
    public String Proficiency {get;set;}
    public Integer YearOfExp {get;set;}
    public String SkillType {get;set;}
    public String TrainingName {get;set;}
    public String CertificationName {get;set;}
}

List<Employee_Skills__c> lstEmpSkills = [SELECT Skill_ID__r.Name, Proficiency__c, Year_of_Experience__c, Skill_Type__c, Contact.Id, Contact.Name, Contact.Email
                                        FROM Employee_Skills__c
                                        ORDER BY Proficiency__c DESC, Skill_Type__c ASC];

Map<Id, WrapperClass> wrapperMap = new Map<Id, WrapperClass>(); 


for(Employee_Skills__c empObj:lstEmpSkills) 
{
    List<WrapperClass> lstWrapper = new List<WrapperClass>();
    if(wrapperMap.containsKey(empObj.Contact.Id))
    {
        lstWrapper = wrapperMap.get(empObj.Contact.Id); 
    }   
    WrapperClass wrapper = new WrapperClass();
    wrapper.contactId = empObj.Contact.Id;  
    lstWrapper.add(wrapper);

    wrapperMap.put(wrapper.contactId, lstWrapper);
}

List<Contact> lstContact = [SELECT Id,
                            , (SELECT Training_ID__r.Name FROM Employee_Training__r)
                            , (SELECT Certification_ID_r.Name FROM Employee_Certifications__r)
                            FROM Contact];

for(Contact con:lstContact)
{
    //now retrive the contact from the wrapperMap
    //verify the same contact Id and assign the TrainingName and CertificationName into the record.
}

Note: I haven't compiled the code, just given you an approach