[SalesForce] SOQL error “cannot have more than one level of relationships”

I have a Facility object that has a relationship with Department object. Now I created a new object to give users access on these facilities.

I'm trying to retrieve Facility data to a list, but I want just to retrieve the facilities that has departments User has access on.

Department object

_____________________________________
|    ID     |   Name   |   Facility  |

|   dep1    |  IT dep  |   Facility1 |
|   dep2    |  HR dep  |   Facility2 |
|   dep3    |  SC dep  |   Facility1 |

access_object

_________________________
| User  |  department   |

| hasan |    dep1       |
| jack  |    dep2       |

This what I tried and gave me this error in the title

Facility = [SELECT ID, Name 
            FROM Facility__c 
            WHERE ID IN (SELECT Department__r.Facility__c 
                         FROM Access_object__c 
                         WHERE User__c =: UserInfo.getUserId())];

Best Answer

Try reworking the query to start from Access_object__c and then group the resulting facility ids and names.

select Department__r.Facility__c, Department__r.Facility__r.Name 
from Access_object__c 
where User__c = :UserInfo.getUserId()
group by Department__r.Facility__c, Department__r.Facility__c.Name
Related Topic