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

Question

I have two different custom objects. Each object is related to both an Account and a Campaign via a lookup field. So both objects have the following fields:

sObject.Account__c
sObject.Campaign__c

So I have a list of objects of the first type, and I want to find all the objects of the second type that have the same Account__c and Campaign__c fields.

I have a working method, I just feel like it's probably inefficient and I want to know if there is a more elegant way to do this.

So right now, my algorithm is basically:

Set<Id> setAcctIds = new Set<Id>();
Set<Id> setCmpgnIds = new Set<Id>();

for(ObjectA oa: listObjectA){
    setAcctIds.add(oa.Account__c);
    setCmpgnIds.add(oa.Campaign__c);
}
List<ObjectB> listObjectB = [SELECT Account__c, Campaign__c, Value__c FROM OBjectB WHERE Account__c IN :setAcctIds AND Campaign__c IN :setCmpgnIds];
for(ObjectA oa: listObjectA){
    oa.value__c = 0;
    for(ObjectB ob: listObjectB){
        if(oa.Account__c == ob.Account__c && oa.Campaign__c == ob.Campaign__c){
            oa.value__c += ob.value__c;
        }
    }
}

update listObjectB;

So my only "problem" with this, and it's more a nitpick, but my only complaint is with the following statement

[SELECT Account__c, Campaign__c, Value__c FROM OBjectB WHERE Account__c IN :setAcctIds AND Campaign__c IN :setCmpgnIds];

This potentially retrieves a lot of objects that I don't need. What I'm really looking for is items that match a specific combination of two variables. Each ObjectA is essentially defined by the Campaign and the Account it's associated with. So for each ObjectA, I want all ObjectB where Campaign AND Account match that ObjectA.

So if we have two different ObjectA

ObjectA1{ aValue1,cValue1 }
ObjectA2{ aValue2,cValue2 }

The above SOQL statement will select

ObjectB1{ aValue1,cValue1 }
ObjectB2{ aValue2,cValue2 }
ObjectB3{ aValue1,cValue2 }
ObjectB4{ aValue2,cValue1 }

Is there any way to select ONLY the ObjectB where BOTH the values appear in a single a ObjectA ?

Answer 1

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.

Answer 2

If oa.Account_c and oa.Campaign_c already exist as shown below in your code where you add them to the sets you declared in your first 2 lines of code,

for(ObjectA oa: listObjectA){
setAcctIds.add(oa.Account__c);
setCmpgnIds.add(oa.Campaign__c);

then I don't think you even need to create the sets you've defined. Instead, all you should need to do is write a query that looks something like this:

> list<ObjectB> listObjectB = [SELECT Account__c, Campaign__c, Value__c FROM ObjectB WHERE Account__c = :oa.Account__c AND Campaign__c = :oa.Campaign__c];

This should return only results for listObjectB that are also contained in listObject A. If you haven't already, you might want to install Ajax Tools into your dev org or Sandbox. Its very helpful when building more complex queries and for looking at relationships.

If there's a "Value__c" condition that you can specify, you could also do something with that as well. It all depends on what you want to do with the results. If you're interested in Grouping or sorting it, you might want to look at the SOCL documentation related to "Group By" options or APEX "List.sort" options that can be applied as query parameters before it's run.