I have two custom objects, Students and Coaches, where Students have a Coach__c lookup field. I am attempting to write a trigger that when a new Student is created, I need to assign that student a Coach.

In order to do this, I need to find out which Coach has the least amount of students, and assign the new student to that coach. In normal SQL, I would use a LEFT JOIN:

SELECT c.id, count(s.Coach__c) AS number_of_students
FROM Coach__c AS c
LEFT JOIN Student__c AS s
ON (c.id == s.Coach__c)
ORDER BY number_of_students

How would I achieve this goal in SOQL?

Or is there a way in which I could count the number of Student__C relationships that Coach__c is used in via the Coach__c object?


You can use SOQL group by clause, In your insert trigger you can use the Aggregate function count on Student table, to group student number vs coachId

   AggregateResult[] groupedResults =[SELECT Coach__c ,count(Id) FROM Student__c 
                                    GROUP BY Coach__c];

Then sort them with one which is least assigned and then assign the newly created student to it.


  • 1
    Can even do the sorting and selection in SOQL as in the SQL: ... GROUP BY Coach__c ORDER BY count(Id) ASC LIMIT 1.
    – David Reed
    Dec 3 '18 at 18:10
  • Ah, I literally forgot about it Dec 3 '18 at 18:12
  • 1
    @PranayJaiswal, @DavidReed I also added ... FROM Student__c WHERE Coach__c != '' GROUP BY... so that empty coaches aren't considered during query
    – BlondeSwan
    Dec 3 '18 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.