Complex SOQL: I need help with a dual join

Question

Since there are way too few options to do literally anything with Account Team Members, we want to use the following approach to track Account Team Member history:

• Have a custom object AccountTeamMemberTracking__c that is a sort of snapshot of all Account Team Member entries
• Have a custom object AccountTeamHistory__c that is supposed to store changes to Account Team Members
• Use a scheduled Apex class to daily compare the Account Team Members against the AccountTeamMemberTracking__c to find changes, additions and deletions and add that as entries to the AccountTeamHistory__c

I was thinking:

• If I look for Account Team Member records with created date today, I will obviously find insertions of new records that I need to add to the history and to the AccountTeamMemberTracking__c.
• If I look for Account Team Member records with change date today, I will obviously find those that changed. To compare them to AccountTeamMemberTracking__c, I need a good query for this
• If I look for AccountTeamMemberTracking__c records, for which there are no Account Team Member records, I detect a deletion of an Account Team Member. For that I need a good query.

I thought I could simply store the Account Team Member Id on the AccountTeamMemberTracking__c object as a unique case sensitive string, but it appears that the SOQL "IN" only works on IDs for Semi-Joins.

So what I was thinking now is that I could use a combination of UserId and AccountId to find matching records because a user can only appear once on an Account as a Team Member or in other words: The combination UserId & AccountId is unique for all AccountTeamMembers. The problem is, the following query will not give me the expected result:

return [    SELECT  Id,
                    UserId,
                    TeamMemberRole,
                    AccountId,
                    LastModifiedById,
                    LastModifiedDate
            FROM    AccountTeamMember
            WHERE   UserId IN (SELECT UserId__c FROM AccountTeamMemberTracking__c)
                    AND AccountId IN (SELECT AccountId__c FROM AccountTeamMemberTracking__c)
                    AND LastModifiedDate >= :system.today()];

The issue here is, that the AND between the UserId IN join and the AccountId behaves more like an OR. It will find me AccountTeamMemberTracking__c records for which an AccountID and also a UserId exists on AccountTeamMember, but not necessarily on the same record. I did a simple test of that and proved my assumption. If you want, I can past that code here too.

Is there any way I can solve this? I know it is doable in SQL. A colleague of mine proved that. But SQL doesn't help me much in Salesforce. Any help is much appreciated...

Answer 1

I don't think that you can do this directly with SOQL.

If you were querying a normal object, you could create a formula field which is a compound of the two things you want to match on, then query on that i.e.

UserId_And_AccountId__c is a text formula on defined as CASESAFEID(UserId) + CASESAFEID(AccountId). Then a similar formula on AccountTeamMemberTracking__c.

If you could do that, then you could query as:

return [    SELECT  Id,
                    UserId,
                    TeamMemberRole,
                    AccountId,
                    LastModifiedById,
                    LastModifiedDate
            FROM    AccountTeamMember
            WHERE   UserId IN (SELECT UserId__c FROM AccountTeamMemberTracking__c)
                    AND AccountId IN (SELECT AccountId__c FROM AccountTeamMemberTracking__c)
                    AND UserId_And_AccountId__c IN (SELECT UserId_And_AccountId__c FROM AccountTeamMemberTracking__c)
                    AND LastModifiedDate >= :system.today()];

(I kept the original fields in the query too, because they are indexed. If you have lots of records, just using the custom field would be too slow).

But, you can't add fields to AccountTeamMember. So, you will have to run the query as you have it, then use Apex to filter it down to just the results you actually wanted.