Need help with SOQL nested query

Question

How do I query a custom object record that matches following scenario.

Custom object has master detail relationship with Account. So I want to rerieve a custom record who's account matches with logged in user's--contact--account. I need it for lightning component so that I can use it on community.

is this achievable with SOQL?

Answer 1

User object has both AccountId and ContactId field populated for a community user.

Simplest query would be using a SOQL join as follows:

SELECT Id, Name, Status__c from Expense__c 
    WHERE 
    Account__c IN (SELECT AccountId FROM User WHERE Id = :UserInfo.getUserId())

As I do not have custom object in my org, adding similar SOQL with standard object for reference which I have verified and works:

SELECT Id FROM Contact WHERE 
    AccountId IN (SELECT AccountId FROM User WHERE Id = :UserInfo.getUserId())

Answer 2

You can do what you want in SoQL, but you're going to have to do that in two steps.

Because Acc->Conctact->User goes "down" in hierarchy and Custom ->Account goes "up", you won't be able to get them together in the same query.

Here is a way to do it:

Id accountId = [select Contact.AccountId from User where Id = :UserInfo.getUserID()].Contact.AccountId;

List<Custom__c> custObj = [select Id from Custom__c where Account__c = :accountId];

UPDATE

As Rahul suggested below

 List<Custom__c> custObj = [select Id 
                               from Custom__c
                               where Account__c in (SELECT Contact.AccountId 
                                    FROM User 
                                    WHERE Id = :UserInfo.getUserId())];

should also work (did not get a chance to test it), though the IN clause makes the query less performant... which may not be a problem in your case.