[SalesForce] Query to get records from 3 object relationship

Setup

I have three objects: X, Y, and Z.
X (child) has master-detail relationship to Y (parent).
Z (child) has a lookup relationship to Y (parent).

Question

How do I retrieve records from object X which are also related to object Z with Y? That is, results from X having common entry in Y.

Best Answer

For simplicity I will rename your objects to:

X => Detail
Y => Parent
Z => Child

The most reasonable interpretation of your OP I can infer is:

If I have a collection of Child records, how can I find Detail records for the same Parent records?

If this interpretation is correct, you can do the following:

public static List<Detail__c> findSiblings(List<Child__c> children)
{
    Set<Id> parentIds = new Set<Id>();
    for (Child__c child : children) parentIds.add(child.Parent__c);
    return [SELECT Id FROM Detail__c WHERE Parent__c IN :parentIds];
}

Another interpretation I could see is:

How can I find all Detail records whose Parent has any Child records?

If this interpretation is correct, I would add a field on Parent along the lines of Has_Child_Records__c, which you can manage from Process Builder. Just set it up on Child to update the Parent checkbox with the criteria OR(ISNEW(), ISCHANGED(Parent__c)). Then you can just do:

public static List<Detail__c> findDetails()
{
    return [SELECT Id FROM Detail__c WHERE Parent__r.Has_Child_Records__c = true];
}

If neither of these interpretations is correct, please add more detail to your question to clarify.

Related Solutions

[SalesForce] Soql query on parents other child object

You can try to query the junction object like this:

Select ObjectC__r.Id, ObjectC__r.Name
From ObjectA__c
Where ObjectC__r.Date__c >= ObjectB__r.start_date__c
And ObjectC__r.Date__c <= ObjectB__r.end_date__c

[SalesForce] Didn’t understand the relationship AccountLocations__r

There's really no need to use table names in your query (e.g. select account.id from account), but if you are insisting on using them, then your second query probably should have been:

SELECT Account__c, AccountLocations__c.Account__r.ID FROM AccountLocation__c

Use __r only to refer to a relationship. The table name doesn't change to __r. This is one reason I tend to avoid table names/aliases in straight queries, because it complicates otherwise easy code.

As a side note, Account__c and Account__r.Id refer to the same value. You really wouldn't normally refer to both of them that way.

The second query wouldn't return that form, although the first would (I presume this what you meant). Extracting the values can be done several ways. One possible way to get just Id values could look like:

Set<Id> ids = new Map<Id, AccountLocation__c>(results[0].AccountLocation__r).keySet();

However, depending on your use case, you might not even need to do that. You can bind an SObject list to a query variable, for example, and the query returns all values that match the IDs in the list.

You could also do this a bit more manually and just loop through the values and add them to a list or set.

Best Answer

Related Solutions

[SalesForce] Soql query on parents other child object

[SalesForce] Didn’t understand the relationship AccountLocations__r

Related Topic