+ Start a Discussion
Edward GeeEdward Gee 

SOQL in Apex Code - multiple condition expressions, both using a correlated set of values

I'm running into the too many SOQL queries on a trigger for a given transaction and I was wondering if there's a code-wise way to solve this problem.

I'm performing a SOQL query that looks like like this:
Code:
for (....) {
TSF_vod__c tsf = [Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c Where Account_vod__c = :acct And Territory_vod__c = :terr];
}

where :acct and :terr are String type. That combination of values will return me
only one row (or none) from my TSF_vod__c object.
When "bulking" up these transactions, I would like to remove the for-loop from the SOQL and perform some post-processing of the result set:
Code:
TSF_vod__c [] tsfs = [Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c
Where Account_vod__c In :accts And Territory_vod__c In :terrs];

where :accts and :terrs are now correlated arrays of ids.

The result set I'm expecting should contain data that the following SOQL would return:

Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c
Where (Account_vod__c=<acctid1> And Territory_vod__c=<terrid1>) Or
(Account_vod__c=<acctid2> And Territory_vod__c=<terrid2>) Or
(Account_vod__c=<acctid3> And Territory_vod__c=<terrid3>) Or ...

In my testing, however, my attempt yielded a result set that I expect the following SOQL to return me which is not what I wanted:
Code:
Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c
Where (Account_vod__c In (acctid1,acctid2,acctid3,...)) And
          (Territory_vod__c In (terrid1,terrid2,terrid3,...))

Is there a way to accomplish what I'm trying to do in my trigger?

- Ed
TehNrdTehNrd
This statment will not pull TSF_vod__c records where Account_vod__ and Territory_vod__c are equal.

TSF_vod__c [] tsfs = [Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c
Where Account_vod__c In :accts And Territory_vod__c In :terrs];
For Example:

:accts contains A,B,C,D,E
:terrs contains A,B,C

If there is a TSF record where accts equals A and terr equals B it will return this record.

What you could do is that once the records are all returned is clean them by checking if the vods match:

Code:
for(TSF_vod__c tsf : tsfs){
       if(tsf.Account_vod__c == tsf.Territory_vod__c){
        add to a clean list
    }
}
I think you could also check to see if they are different and them remove them from the tsft list/array that already exists but you would need to know the index number.

Message Edited by TehNrd on 10-19-2007 09:24 AM

Edward GeeEdward Gee
The lists :accts and :terrs are guaranteed to be a correlated list of values.  Meaning, the entries indexed by N in arrays :accts and :terrs identifies a row of data in my TSF_vod__c record.  Instead of iterating through the lists and invoking a SOQL call to retrieve each TSF_vod__c record, I was hoping there will be a bulk way of grabbing the set of TSF_vod__c rows in one fell swoop.

I had thought of retrieving the set of TSF_vod__c records based on my first array and then eliminating items on a second pass based on the second array but in that scenario, I run into too many rows being returned by TSF_vod__c.

Hmm... maybe I can create a formula field on TSF_vod__c which is a concatenation of those two strings.  That way, I'll have a single criteria expression... kinda hacky but it may work.

Code:
TSF_vod__c [] tsfs = [Select Id,Account_vod__c,Territory_vod__c From TSF_vod__c
                     Where New_Calculated_Field__c In :concatenatedAcctTerrs];

- Ed



TehNrdTehNrd
Even if the :accts and :terrs are guaranteed to be indexed the same I think my example still holds true. Perhaps someone with more SOQL expertise can confirm.

If you would like, can you most some more the actuall code or some more details on the use case?

-Jason