How can I check that parent has more than 1 child?
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');
// ...
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.