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) GROUP BY c.id ORDER BY number_of_students LIMIT 1
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?