0

I have 3 custom objects

  1. Organization__c
  2. School__c
  3. Student__c

The objects are related like below:

  • Object Organization__c has a lookup field School__c which is a lookup on the object School__c.

  • Object School and Student has a master detail relationship where School is parent object and Student is child object.

I actually need to write a SOQL query on the Organization object to get the list of organizations, and in this query itself I have to get the details of the School lookup record and also all the related Student child records of the School.

I am able to write query for getting the Organization and its lookup School record like below:

Select Id, Name, School__r.Id, School__r.Name From Organization__c

Is there any way by which I can modify the above query to also get all the Student child records under the School (School is the lookup on Organization). I basically need a single query which can get the list Organization records, Each organization record returned should also have details of its lookup School record and also the Student child records under the School. ?

Any help would be appreciated

1
  • It sounds a little bit odd that Organization__c is actually a child of School__c. Should be the other way round, just be the the meaning of the names (thinking about clean code here). Are you sure? Sep 28 at 14:53

2 Answers 2

0

You can only go 1 level deep when querying child records. You can also go 5 levels up when querying fields from lookup relations.
But you cannot go up then down, so you need to split those queries up in 2. See https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm for more information on relationship queries.

So SELECT Id, (SELECT Id FROM Students__r) FROM School__c will give you all students related to the school that you're querying.

If you only want to query schools that are related to the organizations you're querying, then you need to filter the school Ids in a for loop and put them in a variable that you then use in the query above.

SELECT School__c FROM Organization__c

//do your forloop and put the School__c values in schoolIds   
SELECT Id, (SELECT Id FROM Students__r) FROM School__c WHERE Id IN :schoolIds

Edit:
After another thought, you can get it by using the School as starting point, something like this:

SELECT Id, (SELECT Id FROM Students__r) FROM School__c WHERE Id IN (SELECT School__c FROM Organization__c) 

Of course applying the WHERE clauses to filter where applicable.

0

The question is confusing, because you state that Organization__c is a child of School__c, but still want to "start" your logic from the organization.

Since you're saying that both Organization__c and Student__c are children of School__c, it's pretty straight-forward to retrieve all data in a single query:

SELECT Id,(SELECT Id FROM Students__r),(SELECT Id FROM Organizations__r) FROM School__c

From there, you can quickly build your list of Organizations. However, this seems more than a coding excercise than a real-world problem. I would recommend to go with the answer from Folkert to simply query organizations and then build your school query from that.

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.