2

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?

Improve this question

1 Answer 1

Reset to default
3

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.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm

Improve this answer
3
  • 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
    Commented Dec 3, 2018 at 18:10
  • Ah, I literally forgot about it
    – Pranay Jaiswal
    Commented Dec 3, 2018 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
    Commented Dec 3, 2018 at 19:20

You must log in to answer this question.

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