[SalesForce] Soql query on parents other child object

I want to write a soql query , My scenario is as follows. I have three objects objectA,objectB,objectC. objectA is the parent object for both objectB,objectC.

objectA – objectB (Master-detail relationship)
objectA – objectC (Master-detail relationship)`

ObjectB has two fields:

Start_date__c
End_date__c

ObjectC has a field:

Date__c

Now i want to retrieve records with date__c in objectC those were in between start_date__c and end_date__c of objectB. how can i achieve such functionality .

Best Answer

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

Related Solutions

[SalesForce] Query to get records from 3 object relationship

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.

[SalesForce] Sharing rule on Detail object

In case of Master Detail:

Yes you cant create sharing rule on detail side of the object.Child objects don’t have a share-record of their own and will be shared along with the master record.

In case of Lookup:

Child objects can have their own sharing access level and ownership. Sharing access through hierarchies can also be disabled.

so simple solution is change the relationship better You can make it Lookup relationship (it depends!!!)

Best Answer

Related Solutions

[SalesForce] Query to get records from 3 object relationship

[SalesForce] Sharing rule on Detail object

Related Topic