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
dke01dke01 

SOQL over 2 master detail relationships

How can I select using Dynamic SOQL rows over 2 master details relationships.

 

Here is my structure

 

Foo --< FooRole  >--  Role__c --< RoleUser >-- User__c

I want to select all Foos that have a Role that the User is in.

 

My first attempt was:

 

SELECT id FROM Foo WHERE Id in 
(SELECT Foo__c FROM FooRole WHERE Role__c in 
  (SELECT Role__c FROM RoleUser__c WHERE User__c = '005S0000000G8KqIAK' ) 
)

 

 

But I got an error "unexpected right parenthsis found,  I assume that SOQL does not like nested IN statements

So next I tried to do 2 seperate SOQL statements like

 

List<fooRole> temp = [SELECT id FROM FooRole WHERE Role__c in 
  (SELECT Role__c FROM RoleUser__c WHERE User__c = '005S0000000G8KqIAK' )

 

Then Say

 

SELECT id FROM Foo WHERE FooRole__r in (temp)

 

But this does not work either?  How can I do this?

The only other solution I could get working  is looping through the Temp array getting the Foo__c ID out of it and and concatenating a string like (id1,id2,id3)

 

 

SELECT id FROM Foo WHERE id in (myListOfFooIDs)

 

But I do not want to do this as myListOfFooIDs could be come quite long.

 

 

 

SuperfellSuperfell

How about

 

SELECT id FROM Foo WHERE Id in 
(SELECT Foo__c FROM FooRole WHERE Role__r.user__c = '005S0000000G8KqIAK')
dke01dke01

This does not work I get an error : " No such column user__c on Role"

 

Is the because there is a join table between   User and Role    called UserRole

 

So I cannot say   Role.user__c   can I? if it is a materdetail   between them  as a Role will have multiple users assigned to it.

 

 

Note this is not using the default role table in salesforce but a different type of role