I have a rogue trigger that inserts duplicate OpportunityContactRoles when it shouldn't.
I can't find the error in my code, but it's not a high-volume problem.
It is, however, a daily low-volume problem that I'm currently cleaning up with DemandTools every morning.
I might decide to schedule the cleanup with batch Apex (since, for our business rules, it's literally just finding records where OpportunityId and ContactId match, deleting all but the oldest one, and making sure the oldest one gets IsPrimary set to TRUE if it isn't).
It's easy to grab the ContactId-OpportunityId pairs in question with SOQL:
SELECT OpportunityID, ContactId FROM OpportunityContactRole GROUP BY OpportunityID, ContactId HAVING COUNT(Id) > 1
However, I'm having a little trouble thinking through the most performant way to grab the actual OpportunityContactRole records (and clump them into groups) in Salesforce.
If this were another database, I could grab everything like:
SELECT OpportunityId||ContactId AS GroupingKey, OpportunityContactRoleId, CreatedDate, IsPrimary FROM OpportunityContactRole WHERE Count(Id) OVER (PARTITION BY OpportunityID, ContactId) > 1
(Sorry if that SQL's off - done from memory.)
(Heck, if it were another database, the whole fix could probably be done in declarative SQL/DML except to facilitate scheduling... [/rant])
Anyway, in Salesforce, the first approach that's coming to mind is:
- Cache the offending ContactId-OpportunityId pairs in an Apex data structure
SELECT Id, ContactId, OpportunityId CreatedDate, IsPrimary FROM OpportunityContactRole WHERE ContactId IN :offendingContactIDs AND OpportunityId IN :offendingOpportunityIDsin an Apex data structure
- Post-process cache #2 with Apex, comparing each record's actual ContactId-OpportunityId pair to make sure it's not a false positive due to the loose nature the query that produced it, throwing out any false positive.
- Do whatever else I was going to do with the cache #2 records, possibly working step #3 into step #4 to minimize loop passes.
Is there a better algorithm?