How to structure objects so this subquery works

Question

I have a custom object that looks like this

MyObject__c
-- Company__c (ref to Account)
-- ParentCompany__c (ref to Account)
-- CompanyType__c (picklist: Parent/Child)
-- (a lot more data and demographics fields)

What I need to do is write a SOQL query that takes an Account Id which can be either a parent or child account Id, but it needs to return the CompanyType__c = 'Parent' MyObject__c record.

This is a new Salesforce implementation, so I have a certain amount of control over the objects. I can edit or change the relationships up in a way to support this action if needed.

I also have control over the Apex. This MyOject__c table gets populated by a nightly scheduled batchable. I could do something with that.

More Info

If the input account id represents a parent company, then this works WHERE CompanyType__c = 'Parent' AND Company__c = :acctId. However, if the Account Id is that of a child account, I still need to get the MyObject__c record for it's parent. And I haven't been able to come up with a subquery that works.

Here's a bit more about the config. Account also has a custom field:

-- Id
-- CorporateParent__c (ref to Account)

There is a 1-to-many relationship from Account to MyObject__c. Also:

• If an Account is the corporate parent, then CorporateParent__c is null.
• For a given MyObject__c, if CompanyType__c = 'Child', then MyObject__c.Company__r.CorporateParent__c == MyObject__c.ParentCompany__c.
• MyObject__c differs from Account in that if it is a 'Parent' record then both Company__c and ParentCompany__c have the same account Id.

If this were SQL query, either of these 2 queries would work:

SELECT Id, Name, etc 
FROM MyObject__c 
WHERE CompanyType__c = 'Parent' 
  AND (
    Company__c = :acctId 
    OR Company__c = (
      SELECT TOP(1) Parent__c 
      FROM MyObject__c
      WHERE Company__c = :acctId
    )
  )

Or:

SELECT Id, Name, etc 
FROM MyObject__c 
WHERE CompanyType__c = 'Parent' 
  AND (
    Company__c = :acctId 
    OR Company__c = (
      SELECT CorporateParent__c  
      FROM Account
      WHERE Id = :acctId
    )
  )

But I cannot seem to get the relationships correct, or the queries. I've seen the error "sObject type 'some-field' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name." so many times. I've tried using IN instead of =. When I try the subquery on Account, it says the data has to be related. When I try the subquery on MyObject__c, it says it has to be on a relationship. I don't know what to try next.

Answer 1

If I understood your schema correctly then below one should work as expected but it will count as 2 SOQL quesries against the governor limits.

Id actId = '0012x000006YkUbAAK';

MyObject__c[] objlist = [SELECT Id, Name 
                         FROM MyObject__c 
                         WHERE Company__c =: actId
                         OR Company__c =: [SELECT CorporateParent__c  
                                           FROM Account
                                           WHERE Id =:actId ].CorporateParent__c];

Let me know if this helps.

Answer 2

Use multiple SObjectTypes in the query

SELECT 
   m.Name, a.Name, ac.Name
FROM 
   MyObject__c m, 
   MyObject__c.Company__r a, 
   MyObject__c.Company__r.CorporateParent__r ac
WHERE
   m.Company__c = :acctId
   OR a.CorporateParent__c = :acctId