How to find all records from one master object to another

soql

I have two objects with a join object between them that has a master/detail relationship to each of them: Loan__c <- TagAssignment__c -> Tag__c. Given a query that gives me a set of loan records (SELECT Id, ... FROM Loan__c WHERE ...), how can I construct a query that will give me all of the unique tags that this set of loans relates to through the TagAssignment__c object?

This doesn't work, but I think it captures the logic I'm looking for:

SELECT Id, Name
FROM Tag__c
WHERE TagAssignments__r.Loan__c IN
(SELECT Id FROM Loan__c WHERE ...)

Best Answer

I think you got pretty close.

The issue is that Loan__c is the parent, and TagAssignment__c is the child. You need to use a parent-child subquery or a semi-join (or anti-join) to get at child data in a query (so TagAssignments__r.Loan__c is not valid as you're using it). Parent-child subqueries can only appear in the top-level query (i.e. not within subqueries or semi/anti joins).

Luckily, all you really need to do here is to change your WHERE clause.

TagAssignment__c contains Ids for Loan__c records, so that's what you want your semi-join to return

SELECT
    Id, Name
FROM
    Tag__c
WHERE
    Id IN (SELECT Tag__c FROM TagAssignment__c WHERE Loan__c IN :loanIds)

That will just give you all Tags for all Loans that you gave Ids for. If you want to group the Tags under each individual Loan, you'll need to do that in Apex... and probably include a parent-child subquery so you can actually get at the Loan Ids

If that's what you're looking for, then you could add something like (SELECT Loan__c FROM TagAssignments__r WHERE Loan__c IN :loanIds) to your SELECT clause.

Related Topic