1

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.

0

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.

| improve this answer | |
  • It produces the following error: ERROR running force:data:soql:query: Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions. – jwatts1980 Jun 7 at 21:54
  • I have updated my answer, please check and confirm. – gs650x Jun 8 at 4:35
  • No sorry, it has to be a SOQL query. I cannot use DML in the context. I have control over the structure of the data, the relationships, and how the data gets populated into the table, but not how the SOQL query gets executed. – jwatts1980 Jun 8 at 5:02
  • above SOQL won't server the purpose? – gs650x Jun 8 at 5:07
  • No. Those are DML statements, not SOQL statements. They cannot be directly translated into SOQL. If you open the developer console, go into the Query Editor, that will not be recognized as valid SOQL. – jwatts1980 Jun 8 at 5:27
0

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

| improve this answer | |
  • This seems to work if the account Id is that of a Parent, but not if it is a Child. The ideal query would return the same results if the account Id was a child account. – jwatts1980 Jun 9 at 14:26
  • You can get the parent of the Account from theCorporateParent__c lookup field. Eg. CorporateParent__r.Name, CorporateParent__r.Description, etc. – Hengky Ilawan Jun 9 at 14:37
  • As noted in my question, I am interested in the MyObject data, not the account data. I need the MyObject records that relate to a parent account regardless of whether the account Id is that of a parent or child. – jwatts1980 Jun 9 at 14:49
  • Ah OK. I edited my answer. Hope that is what you needed. – Hengky Ilawan Jun 9 at 15:14
  • No sorry, that still does not work. The ideal SOQL query would return the same result with a Parent or Child acctId. This is the same as the previous suggestion, it gets all the right things if I pass a parent account Id, but nothing if I pass a child Id. If the :acctId is that of a child, MyObjects2__r is empty because that is the link to MyObject.ParentCompany__c and a child account has no references to the ParentCompany__c column. MyObjects__r also doesn't work because of the CompanyType__c = 'Parent' filter, as a child account will never be classified as a 'Parent' in MyObject. – jwatts1980 Jun 9 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.