Writing (what I believe to be) a Left Anti Join in SOQL

soql

So the end result I'm hoping to achieve is to do a Left Anti-Join in SOQL without using Roll-Up Summary fields or Apex. Here's the scenario:

We have 2 custom objects, say Property__c and Connected_Device__c.

Connected_Device__c has the 2 following fields:

A lookup field to Property__c
a picklist field named Type__c with the picklist values: 'Hub', 'Thermostat', 'Garage'

meaning our property records will have a related list of connected devices. Here's the scenario:

Property A has 3 connected devices, one for each of the different Type__c values.
Property B has 2 connected devices, one for Thermostat and one for Garage.

If I wanted to query the properties that had at least 1 connected Hub devices, I could use the query:

SELECT Property__c, COUNT(Id) hubs
FROM Connected_Device__c
WHERE Type__c = 'Hub' 
GROUP BY Property__c
HAVING COUNT(Id) >= 1

But what if I wanted to find all the properties that have 0 connected Hub devices but that still have other linked connected devices (i.e. Property B)?

I wouldn't be able to build a query on the Connected_Device__c records that have Type__c = 'Hub', since the query result would always be empty.

And using a NOT() statement (i.e. (NOT Type__c = 'Hub')) would result in both property records being returned, since both have Thermostat and Garage connected devices.

I know that ultimately I could build a simpler SOQL query and then use logic in Apex to to get the result I want, or have Roll-Up Summaries on the object that count the number of Hub and non-Hub connected devices, but this is more of an exercise in determining if this is possible with SOQL alone.

Let me know if I need to add more to the question/clarify anything!

Best Answer

It'd be something like:

SELECT Id
FROM Property__c
WHERE Id NOT IN (SELECT Property__c FROM Connected_Device___c WHERE Type__c = 'Hub')
  AND Id IN (SELECT Property__c FROM Connected_Device__c)

Note that you must use AND, as OR isn't supported. The Id IN and Id NOT IN syntaxes are incredibly useful for finding parent records that meet certain child criteria.

Related Solutions

[SalesForce] Best way to do a count() from a custom object having more than 1 million records

I think you cannot get the complete count using a SOQL, not 100% sure. Initially I thought you could use a @readOnly annotated method and then get the count but then i think that cannot be done as the you will be executing the query from the context of a VF page. But I can think there are ways of doing this, As of now I can think of 2 methods(I am sure there will be easier way of doing this):

1.) You can make a http request using the REST API. Before using this code make sure you add the salesforce url in the remote site setting. The response you get contains contains a atribute called "totalSize", which gives you the count of number of rows returned by the query (which is mentioned in the REST URL).You can run the below code in the developer console to check. to get the use the below code:

    HttpRequest req = new HttpRequest();
 req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v20.0/query/?q=SELECT+name+from+Account');
 req.setMethod('GET');

string autho = 'Bearer '+ userInfo.getsessionId();
 req.setHeader('Authorization', autho);

 Http http = new Http();
 HTTPResponse res = http.send(req);
 string response = res.getBody();
 string totalCount = response.substring(response.indexOf('totalSize":')+11, response.indexOf(','));
system.debug('get the total count '+ totalCount);

2.) As you mentioned you are using a VF page, you can do the rest call in the VF page itself which is easier way, using jquery:

$.ajax({
url : "https://yourinstance/services/data/v20.0/query/?q=SELECT+name+from+Account",
headers : {"Authorization": "Bearer "+ AddSessionId}, //make sure you add the sessionId
contentType : "application/json"
}).done(function(response){
    console.log('The count is '+ response.totalSize);

});

[SalesForce] Counting notes crashes with OPERATION_TOO_LARGE: exceeded 20000 distinct ids

There are couple of options that I can think of to solve this problem.

Use ReadOnly annotation so that the limit on the data that is returned is increased. Check out this link. Here is one more.
Use filters to narrow down the resultset
Check out this post from boards.developerforce.com. To quote from this post,

The query has returned too many results. Some queries, for example those on objects that use a polymorphic foreign key like Task (or Note in your case), if run by a user without the "View All Data" permission, would require sharing rule checking if many records were returned. Such queries return this exception because the operation requires too many resources. To correct, add filters to the query to narrow the scope, or use filters such as date ranges to break the query up into a series of smaller queries.

Hope this helps.

Related Topic