[SalesForce] SOQL query to show contacts and accounts located within a state that have a certain custom object relationship

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.

Best Answer

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

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] SOQL query help using specific combinations of two variables that also appear in another object

You could put a text formula field on both objects. Something like, AccountId_And_CampaignId__c that is a concatenation of Account__c and Campaign__c.

Then your code becomes:

Set<String> acctCampIds = new Set<String>();
for(ObjectA oa: listObjectA){
    acctCampIds.add(oa.AccountId_And_CampaignId__c);
}

List<ObjectB> listObjectB = [
    SELECT Account__c, Campaign__c, Value__c 
    FROM OBjectB 
    WHERE AccountId_And_CampaignId__c IN :acctCampIds
];

Then for:

ObjectA1{ 'aValue1cValue1' }
ObjectA2{ 'aValue2cValue2' }

You'd get:

ObjectB1{ 'aValue1cValue1' }  // Would find it.
ObjectB2{ 'aValue2cValue2' }  // Would find it.
ObjectB3{ 'aValue1cValue2' }  // would not find it.
ObjectB4{ 'aValue2cValue1' }  // would not find it.

Best Answer

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

[SalesForce] SOQL query help using specific combinations of two variables that also appear in another object

Related Topic