SOQL Account query for custom object related to opportunities

Question

I'm trying to figure out how to query a custom object that is related to opportunities.

The object name is McaApp__Offer__c The lookup field for that object is McaApp__Opportunity__c (master-detail)

This is what I have, but I'mk missing something as this object is not related to accounts, what do I need to change?

SELECT id, Name, 
  ( 
    Select Id, Name From Opportunities ORDER BY Id DESC LIMIT 1 
  ), 
  ( 
    SELECT McaApp__Funder__c, McaApp__Status__c FROM McaApp__Offers__r WHERE McaApp__Opportunity__c = 'oppidxxx' 
  )   
FROM Account 
WHERE id = 'acctidxxx'

Answer 1

You can't query McaApp__Offer__c from within Account as there is no direct relationship. Account < Opportunity < McaApp__Offer__c this is how it realted.

SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

You can do like this.

SELECT Id, Name, AccountId, 
     (SELECT McaApp__Funder__c, McaApp__Status__c 
     FROM McaApp__Offers__r) 
FROM Opportunity 
WHERE AccountId = 'acctidxxx'
LIMIT 1