0

I'm trying to create an SOQL query with a subquery to show contacts and accounts located within a state that have a certain custom object relationship:

Here is my code:

    public with sharing class AccountsinPlacesExt {
    @AuraEnabled

        public Place__c place { get; private set; }
        public List<Account> inPlaceAccounts { get; private set; }
        public AccountsinPlacesExt(ApexPages.StandardController controller)
        {
            // add the Place__c field that tells you which state
            List<String> neededFields = new List<String> { 'City__c','State_Code__c' };
            if (!Test.isRunningTest()) controller.addFields(neededFields);
            place = (Place__c)controller.getRecord();

           if (place.City__c != null) {
           // search for city and state match
                inPlaceAccounts = [ 
                    SELECT Id,Name,Our_Role_Service_Affiliation_for_Account__r,(SELECT Name FROM Contacts) FROM Account WHERE Our_Role_Count__c > 0 AND BillingCity = :place.City__c AND BillingState = :place.State_Code__c AND Id IN (SELECT Account__c FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')
                ];
           } else {
           //search for only state match
                inPlaceAccounts = [
    SELECT Id,Name,Our_Role_Service_Affiliation_for_Account__r,(SELECT Name FROM Contacts) FROM Account WHERE Our_Role_Count__c > 0 AND BillingState = :place.State_Code__c AND Id IN (SELECT Account__c FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')
               ];
           }
           // end else
        }

    }

Now I'm still seeing:

I tried but it isn't working now. even in the dev console I'm seeing an error: SELECT Id,Name,Our_Role_Service_Affiliation_for_Account__r ^

ERROR at Row:1:Column:16 No such column 'Our_Role_Service_Affiliation_for_Account__r' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

1
  • Have you tried to use just Account__c instead of Account__r.Id? Also in your inner query you can just query the account's Ids. The other values are not necessary. Mar 18, 2017 at 1:14

1 Answer 1

3

When you try to filter results based on a separate query, that is called a Left Inner Join.

SELECT Name FROM Position_c WHERE Id IN
(SELECT Position__c FROM Job_Application__c)

When you try to pull in parent fields in a SELECT clause, it's called a Right Outer Join.

SELECT Name, Position__r.Department__c FROM Job_Application__c

You cannot combine these types of join. But you're needlessly complicating things, since SomeLookup__r.Id is always the same value as SomeLookup__c. So in this case, just use that. You also have to remove all other fields, the only field you are allowed to have in a Left Inner Join query is the field you are filtering on. So change your join to:

(SELECT Account__c FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')

Any fields you want to retrieve from the queried records must be in the top-level SELECT clause.

SELECT Name, OtherFieldsToRetrieve__c FROM Account
4
  • Left, right, outer, inner, I still can never remember which is which. I just know I want to pull in lookup, detail records, or filter based on a query lol.
    – Eric
    Mar 18, 2017 at 3:29
  • I updated my question as I now am getting a new error...
    – soxdave
    Mar 18, 2017 at 14:25
  • @soxdave You need to put the fields in your top-level select clause...
    – Adrian Larson
    Mar 18, 2017 at 18:36
  • the other fields are related to another object.. and i want to pull records related to those. is that not possible? it works as written in the developer console but when i put it in the apex page it doesn't work as i'm not calling it from the account page but rather from a custom object called Places.
    – soxdave
    Mar 19, 2017 at 21:04

You must log in to answer this question.

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