function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
eliotstockeliotstock 

SOQL: limit parent records returned by child IDs

Hi there

 

I have two custom objects with a master-detail relationship, CustomProject__c and CustomPayment__c. CustomProject__c is the parent and can have many payments. I have a set of IDs of payments and want to find all the parents of these payments.

 

 

SELECT Id, Name, TotalPayments__c, (SELECT Id, Amount__c FROM CustomPayments__r WHERE Id IN :paymentIds)
FROM CustomProject__c
 

 
 
I would expect this to give me just the CustomProject__c parents that have CustomPayment__c children with IDs in the list paymentIds. It doesn't. Instead I get *all* CustomProject__c parents. Can anyone shed any light on this?
 
Thanks,
 
Eliot Stock.
Message Edited by eliotstock on 05-11-2009 06:04 AM


Best Answer chosen by Admin (Salesforce Developers) 
werewolfwerewolf
No, not true at all.  Queries can look both directions. 
 
SELECT CustomProject__r.Id, CustomProject__r.Name, CustomProject__r.TotalPayments__c, Id, Amount__c FROM CustomPayments__c WHERE Id IN :paymentIds
 


All Answers
werewolfwerewolf
Why not just reverse it such that the CustomPayments__c is the main query and CustomProject__c is the subquery?  That'll probably fix it. 


eliotstockeliotstock
Pretty sure you can't do this. The child has to go in the subquery and the parent in the outer query.


werewolfwerewolf
No, not true at all.  Queries can look both directions. 
 
SELECT CustomProject__r.Id, CustomProject__r.Name, CustomProject__r.TotalPayments__c, Id, Amount__c FROM CustomPayments__c WHERE Id IN :paymentIds
 
This was selected as the best answer
eliotstockeliotstock
Ok but that's not a subquery, that's dot notation (one is for child to parent and the other for parent to child). And it's no longer a query for CustomProject__c, it's now a query for CustomPayment__c. The problem with that is the very next thing I need to do is iterate over the result set and deal with projects.


werewolfwerewolf
Well my query does return the set of records that you want -- the payments and only the projects that are related to them.  So iterate on this set.  It really doesn't matter what's a subquery here.


eliotstockeliotstock
True enough I guess. I'll see if I can make it work for what I need to do next. Thanks.


werewolfwerewolf
To your original question, the reason your query didn't work is that the main query won't scope itself to the subquery.  Think about what your main query would be if that subquery weren't there:
 
SELECT Id, Name, TotalPayments__c FROM CustomProject__c 
 
That would just return all CustomProject__c, which is in fact what it's doing.  The WHERE in your subquery only scopes those subquery results.  That's why you had to flip the query to get it to work.