2

I'm trying to build a SOQL query to show the Name, Services and Contacts for accounts that have a relationship to a particular custom object, but I feel I'm missing something here:

SELECT Name, ServiceAccountAssociation__r.Name,
       Account.Contact.Name 
  FROM Account 
 WHERE ((Id IN (Select Account__c.Id from ServiceAccountAssociation__c)) 
   AND (BillingState = 'MA'))

The error I"m getting is:

SELECT Name,ServiceAccountAssociation__r.Name ^ ERROR at Row:1:Column:13 Didn't understand relationship 'ServiceAccountAssociation__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

The relationship is as follows:

Accounts are related to Service__c (custom object) via the ServiceAccountAssociation__c custom object. There is a master-detail relationship called Service__c on the ServiceAccountAssociation object which connects Service to Account.

I've looked at various salesforce documentation and am getting really lost...

  • SELECT Name, ServiceAccountAssociations__r.Name ^ ERROR at Row:1:Column:14 Didn't understand relationship 'ServiceAccountAssociations__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. – soxdave Feb 19 '16 at 16:10
  • Line: 1, Column: 32 serviceaccountassociation__c is not a field of Account – soxdave Feb 19 '16 at 16:23
  • There is a custom object ServiceAccountAssociations that joins the Account and Services object together – soxdave Feb 19 '16 at 16:24
  • There isn't a field on the Account record that hosts that information. Did I build it wrong? – soxdave Feb 19 '16 at 16:25
  • You can add a Roll Up Summary field on Account if ServiceAccountAssociation__c has a Master-Detail relationship to it. – Adrian Larson Feb 19 '16 at 17:11
2

Recommended - Roll Up Summary

If you have a Master-Detail relationship from ServiceAccountAssociation__c to Account, you can create a Service_Count__c roll up summary. Once you create the field, Select Roll-Up Type > COUNT. Then you can query Account directly without needing any cross-object SOQL.

SELECT Name FROM Account
WHERE Service_Count__c > 0
AND BillingState = 'MA'

Alternative - Using Apex

SubQuery

If you are using a junction, you likely have a relationship FROM ServiceAccountAssociation__c TO Account.

You want to find the child relationship, which you can do as follows:

DescribeSObjectResult describe = SObjectType.Account;
for (ChildRelationship relationship : describe.getChildRelationships())
{
    if (relationship.getChildSObject() == PermissionSetAssignment.SObjectType)
        system.debug(relationship.getRelationshipName());
}

Assuming it were ServiceAccountAssociations__r, you could do a sub-query:

SELECT Name, (SELECT Service__c, Service__r.Name FROM ServiceAccountAssociations__r)
FROM Account WHERE ...

Aggregates

You may need to query the junction table itself if you want to figure out which Accounts are tied to any Service__c Try to describe the lookup field from ServiceAccountAssociation__c to Account and get its relationship name. Assuming the field is also called Account__c:

DescribeFieldResult describe = ServiceAccountAssociation__c.Account__c.getDescribe();
system.debug(describe.getRelationshipName());

Assuming it is Account__r, you could make queries against the junction table.

List<AggregateResult> junctionAggregates = [
    SELECT COUNT(Id) records, Account__c FROM ServiceAccountAssociation__c
    WHERE Account__r.BillingState = 'MA'
    GROUP BY Account__c
    HAVING COUNT(Id) > 0
];

Take a look at how the HAVING clause works.

Tying It Together

You can use the two above queries together to list all Service names associated to an Account:

Set<Id> accountIds = new Set<Id>();
for (AggregateResult aggregate : /*aggregateQuery*/)
    accountIds.add(aggregate.get('Account__c'));
List<Account> accounts = [SELECT Name, /*subquery*/ FROM Account WHERE Id IN :accountIds];

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.