4

Now I'm trying to use this to check it:

List<Tool__c> tools = [SELECT Id FROM Tool__c WHERE Date__c >= 2019-09-11T00:00:00.000Z];

for(Tool__c t : tools){
    //And I'll get here 101 limits because of SOQL in the loop, yeah:
    List<Gear__c> gears = [SELECT Id FROM Gear__c WHERE Tool__c =: t.Id ORDER BY CreatedDate];
    if(gears.size() > 1){
        System.debug(gears);
    }
}

I'm also trying this fragment:

List<Tool__c> tools = [SELECT Id, (SELECT Id FROM Gears__r) FROM Tool__c WHERE Date__c >= 2019-09-11T00:00:00.000Z ORDER BY Id];

But I still have no idea, how to do it right. Help me, please.

9

You're pretty close to a solution with your parent-child subquery.

The big thing to realize is that, similar to how adding parent__r.someField__c gives you a real SObject instance nested in your query results, a parent-child subquery gives you a List<SObject> nested in your query results.

About the only thing I'd add is to limit the subquery rows to 2 to keep the number of queried rows down (you don't say that you care about having any number bigger than 2, just that it's 2 or more).

for(Tool__c tool :[SELECT Id, (SELECT Id FROM Gears__r LIMIT 2) FROM Tool__c WHERE Date__c >= 2019-09-11T00:00:00.000Z ORDER BY Id]){
    //  Gears__r is just a List<SObject>, meaning we can call any of the methods
    //    that the List object provides (such as size() )
    if(tool.Gears__r.size() > 1){
        // Parent tool has >= 2 gears
    }
}

Normally, the tool.Gears__r.size() line can be a little dangerous. Salesforce wants us to use a nested loop to access child records from a parent-child subquery where there are enough child records to warrant an internal call to queryMore(). Limiting the child records to a max of 2 per parent means that you shouldn't have to worry about that here.

13

You can use a HAVING clause:

AggregateResult[] results = [SELECT COUNT(Id) sum, Tool__c Id FROM Gear__c WHERE Tool__c = :tools GROUP BY Tool__c HAVING COUNT(Id) > 1];

This will give you a list of AggregateResult objects where the sum and Tool__c Id are given. You can even use the Map trick to get the count per record easily:

Map<Id, AggregateResult> results = new Map<Id, AggregateResult>([
  SELECT COUNT(Id) sum, Tool__c Id FROM Gear__c WHERE Tool__c = :tools GROUP BY Tool__c HAVING COUNT(Id) > 1
]);
for(Tool__c record: tools) {
  AggregateResult result = results.get(record.Id);
  if(result != null) {
    Integer totalGears = (Integer)result.get('sum');
    // ...
  • 1
    map trick -- you are referring to bigass Building a Set from any field ? – cropredy Sep 16 at 23:38
  • 1
    @cropredy Yes, I've known about the Id variant of the trick for at least four years. The "any field to a set" trick is newer and really awesome; an incrementally better version that was (presumably) independently discovered. – sfdcfox Sep 17 at 0:57
  • Map trick is neat, – Pranay Jaiswal Sep 17 at 18:49

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.