Stuck on Relationship Query

Question

I am trying to return a list of Accounts

WHERE 

(

All Assets on the Account have Asset.Support_Status__c = 'Canceled' 

-OR- 

There are NO assets on the Account at all

)

AND 

The Account has a contact with Contact.Support_User__c <> Null

---

Both Contact and Asset have a lookup relationship to Account.

Asset.Support_Status__c is a picklist and one of the values is 'Canceled'

All of the queries I have created either do not return the list of accounts I am looking for or error out.

Is this Query possible and can you provide any tips or suggestions on how I can create it or should I use Apex for this?

Answer 1

I think the following should work:

SELECT Id
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Asset WHERE Support_Status__c <> 'Canceled') AND
      Id IN (SELECT AccountId FROM Contact WHERE Support_User__c <> NULL)

Unfortunately, this may not work for "normal" (non-admin users) if you have too many records ("non-selective query" errors). However, if the intent is just to find records as an administrator, you should be okay; you can run this in the data loader and you'll eventually get a response.

Answer 2

You cannot do this in a single query.

You can do it in a single query if you're clever like an @sfdcfox, although I think it's right to say that performance could be an issue in production use. There's a limit of two semi-join sub-selects on a single SOQL query, and yours can be factored to have exactly two.

You can do it in Apex, although I would caveat that by saying that if you have high data volume, there are more considerations that go into both the Apex and the solution below. The queries are likely to be expensive.

However, I think there's another potential option here: Declarative Lookup Rollup Summaries. If you were able to use this package (or another one providing similar programmatic rollup functionality), you could create three roll-up summaries on Account and one formula field:

1. Count_of_Assets__c
2. Count_of_Canceled_Assets__c where Support_Status__c = "Canceled".
3. Count_of_Contacts_Support__c where Support_User__c != null.
4. Checkbox formula field All_Assets_Canceled__c: Count_of_Assets__c = Count_of_Canceled_Assets__c

Then, your query becomes not just possible, but easy. In pseudo-SOQL:

SELECT SOME_FIELDS 
FROM Account
WHERE (All_Assets_Canceled__c OR Count_of_Assets__c = 0) AND Count_of_Contacts_Support__c > 0

Answer 3

I can't think of a way to filter by "All Child's Field == Value", I think you'll need a formula field to figure this out. Sometimes you need to do some math thats too complex to use in a query, but if you use formula fields correctly, you can figure out those values and filter based on it.

I'd create a roll-up summary field, called "Total Assets", to get the total number of Assets, then another roll-up summary field named "Number of Cancelled Assets", filtered to only cancelled values.

Finally, create a formula field named "All Assets Cancelled" with a return of boolean and use this formula:

Total_Assets__c == Number_of_Cancelled_Assets__c

Then write a query like this:

SELECT Id FROM Account WHERE (All_Assets_Cancelled__c = true OR Total_Assets__c = 0) 
AND Some_Contact_Field__r.Some_Field__c != null

Not 100% sure what you're trying to do with your second query filter, but I'm sure you can figure it out.