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...

5
  • 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
    Commented Feb 19, 2016 at 16:10
  • Line: 1, Column: 32 serviceaccountassociation__c is not a field of Account
    – soxdave
    Commented Feb 19, 2016 at 16:23
  • There is a custom object ServiceAccountAssociations that joins the Account and Services object together
    – soxdave
    Commented Feb 19, 2016 at 16:24
  • There isn't a field on the Account record that hosts that information. Did I build it wrong?
    – soxdave
    Commented Feb 19, 2016 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
    Commented Feb 19, 2016 at 17:11

1 Answer 1

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];
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .