How do I count records on a child custom object from the parent?

Question

I am writing a query that calculate and NPS Score on the Account record. There is an NPS custom object related to the Account object through a master-detail relationship. How do I link the account record to the NPS custom object such that I can do a count etc of those objects which are related to a certain account? Right now this is the base of my trigger, how would I go about accessing the child records? I've tried some SOQL queries but it doesn't seem to work. I'm not sure if there is something specific i have to do for parent-child relationships

trigger CalculateNPS on Account (before update) {    
          for(Account a : trigger.new){
             List<NPS> nps= new List<NPS>();
             nps = [SELECT Id FROM NPS__c WHERE =: a.Id];
              a.NPS_Score_Trigger__c = 2;         
          }
}

Answer 1

You can use SOQL Aggregate Queries in your Account trigger.

for (AggregateResult ar : [
     Select Count(Id) numRecs, Account__c acctId
     From NPS__c
     Where Account__c In :Trigger.New
     Group By Account__c
]) {
    Id acctId = (Id) ar.get('acctId');
    Account acct = Trigger.newMap.get(acctId);
    acct.NPS_Score_Trigger__c = (Decimal) ar.get('numRecs');
}

This assumes that the API name for Account is Account__c.

Answer 2

Never have a soql inside a for loop :

trigger CalculateNPS on Account (before update) {    
          for(Account a : trigger.new){
             //List<NPS> nps= new List<NPS>();
             //you do not have to query account related fields inside the account trigger. Use soql only when you have to access related object fields from account.
             //nps = [SELECT Id FROM NPS__c WHERE =: a.Id];
              a.NPS_Score_Trigger__c = 2;         
          }
}

IF THE NPS_SCORE_TRIGGER__C is a text/number field the output when you edit and save the record should be 2 ( you are assigning a defined number to the field).

If you need the actual count of the children NPS associate with the account then

UPDATED:

I did this purely to satisfy my ego. Having maps and query will be bulkier than what peter has answered below. I would recommend using his solution, this is one other way of tackling this issue :)

 trigger CalculateNPS on Account (before update) {     
       map<string,decimal> account_map = new map<string,decimal>();        
    for(Account a :[SELECT Id,(select id from NPS__r) FROM Account WHERE Id IN: trigger.new])
         {
          account_map.put(a.id,a.NPS__r.size());       
         }
for(account a: trigger.new){
if(account_map.containsKey(a.Id){
a.NPS_Score_trigger__c = account_map.get(a.Id);
}
}
      }

Answer 3

I think the best ay will be use those Master-Detail relation. Instead of code use Roll-up fields.

If you must have it in code it is better calculate from child to parent. For example when you want just set number of child just put methode in child trigger (after insert, before delete).