0

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
3
  • 1
    What data types are they. Can you provide some sample values? With child data, you could have multiple values. Would you want to take the maximum? Minimum? Average?
    – Adrian Larson
    Mar 2, 2017 at 4:13
  • So am I right to consider, proficiency as level 1 sort and skill level 2 sort?
    – Mahmood
    Mar 2, 2017 at 4:35
  • @lone, did any of the below answers help you? thanks!
    – glls
    May 8, 2017 at 5:24

2 Answers 2

0

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.

5
  • Hi John! Thanks for your response. Do you think there's another way to get that kind of result? It's not possible even in apex?
    – lone
    Mar 2, 2017 at 1:48
  • If you could do this how would you handle multiple child objects? For example, if you had a contact with two related Employee_Skill__c records with two different values for the Proficiency? You could probably come up with a way to handle the sort in Apex but you would need to consider how you would handle cases like that in order to plan a solution. Mar 2, 2017 at 2:09
  • Can you give us an advise or suggestion how can we achieve that result? We've been trying to figuring it for almost 2 weeks. We need to display the result in that way.
    – lone
    Mar 2, 2017 at 5:27
  • It's difficult to give you a suggestion without knowing the answer to the question in my last comment. Mar 2, 2017 at 17:47
  • Hi, In that case, we need to sort the result based on the Proficiency. Actually, the results need to sort in this order: Skill Type, SkillName, Proficiency, Year of Experience, Training Name, Certification Name and Employee Name.
    – lone
    Mar 14, 2017 at 0:30
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .