SOQL Assistance Please

Question

I am trying to run a SOQL query on my Account Object pulling data from a custom Object A that contains the Account name and it's Global Enterprise and Main HQ. It's a hierarchy of sorts.

In a list view, I am trying to exam if the account that I am on has a Main HQ and if it does, it needs to pull in all of the available Opportunities for every account that has Main HA = Company A. This is the same for accounts where the Global Enterprise is Company Z.

If the Global Enterprise and Main HQ fields are populated on the account, then the list view should show for each of them them separately.

I am new the apex classes and SOQL and would like some assistance on the class and SOQL call out.

public class Examplefilter {

    Public Static List<Custom_ObjectA> searchforCustom_ObjectA (string Global_Enterprise__c, string Main_HQ__c, String Opportunity__r.Name, String Account_Name__r.Name) {
        Return[SELECT Id, Opportunity__r.Name, Account_Name__r.Name, Main_HQ__c, Global_Enterprise__c
              FROM Custom_ObjectA
              WHERE Main_HQ__c = :Main_HQ__c AND Global_Enterprise__c = :Global_Enterprise__c];
    }
}

Answer 1

You provided the following code for your example filter:

public class Examplefilter {

    Public Static List<Custom_ObjectA> searchforCustom_ObjectA (string Global_Enterprise__c, string Main_HQ__c, String Opportunity__r.Name, String Account_Name__r.Name) {
        Return[SELECT Id, Opportunity__r.Name, Account_Name__r.Name, Main_HQ__c, Global_Enterprise__c
              FROM Custom_ObjectA
              WHERE Main_HQ__c = :Main_HQ__c AND Global_Enterprise__c = :Global_Enterprise__c];
    }
}

Since you're passing in string Global_Enterprise__c, string Main_HQ__c, String Opportunity__r.Name, String Account_Name__r.Name you need to construct a "query string" using those values. There are a couple of different ways of doing that as described in the Dynamic SOQL section of the Apex Docs.

Generically it would look like: List<sObject> sobjList = Database.query(string).

However, you have a "where clause" in your query which makes things a bit different. That clause has to be resolved and handled a bit differently as in:

String resolvedField1 = myVariable.field1__c;
List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE field1__c = ' + resolvedField1);

So, in your case, you'll need to do something like the following:

String resolvedField1 = myVariable.Main_HQ__c;
String resolvedField2 = myVariable2.Global_Enterprise__c;
Return Database.query('Id, Opportunity__r.Name, Account_Name__r.Name, Main_HQ__c, 
   Global_Enterprise__c FROM Custom_ObjectA WHERE Main_HQ__c = ' + resolvedField1 + 
   'AND  Global_Enterprise__c = ' + resolvedField2);