How can I check that parent has more than 1 child?

Question

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.

Answer 1

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.

Answer 2

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');
    // ...