+ Start a Discussion
Andy Kallio 7Andy Kallio 7 

Admin struggling with a soql

HI All. The query is on a junction object called Project_Contact_Role__c which links the Project__c object to the Contact object. The org has another junction object called datapm__OptinmOptin__c, which links the same two objects (Project__c and Contact). What I want the query to do is find Project_Contact_Role__c records that do not have a corresponding record in the other junction table. 

So, I've written this query, but it doesn't work quite right. It works if there are no records in datapm__OptinmOptin__c with either same contact id or project id, but if there is a datapm__OptinmOptin__c with same project id and a different contact id or vice versa then it fails. 
select Id, 
  	Project__c, 
  	Contact__c 
  from Project_Contact_Role__c
  where Id IN :pcrs
  and Contact__c NOT IN (select datapm__Contact__c from datapm__OptinmOptin__c)
  and Project__c NOT IN (select Project__c from datapm__OptinmOptin__c)

The only thing I can think of doing is adding a lookup from datapm__OptinmOptin__c to Project_Contact_Role__c. And then I can change the query to this:
select Id, 
  	Project__c, 
  	Contact__c 
  from Project_Contact_Role__c
  where Id IN :pcrs
  and Id NOT IN (select Project_Contact_Role__c from datapm__OptinmOptin__c)

Just want to see if there is anyone that can see a way to fix the first query. Thanks!!
 
Best Answer chosen by Andy Kallio 7
Pradeep SinghPradeep Singh
Hi, you can do somthing like this. I haven't run the code, please let me know if it gives any error.
Map<string,List<Project_Contact_Role__c>> projectContactRoleMap = new Map<string,List<Project_Contact_Role__c>>();
Map<string,List<datapm__OptinmOptin__c>> datapmMap = new Map<string,List<datapm__OptinmOptin__c>>();
List<Project_Contact_Role__c> UniquepcrList = new List<Project_Contact_Role__c>();

for(Project_Contact_Role__c pcr : [SELECT id,Project__c, Contact__c from Project_Contact_Role__c where Id IN :pcrs]){
	if(!projectContactRoleMap.containskey(pcr.Contact__c + pcr.Project__c)){
	     projectContactRoleMap.put(pcr.Contact__c + pcr.Project__c,new List<Project_Contact_Role__c>{pcr});
	}
	else{
	    projectContactRoleMap.get(pcr.Contact__c + pcr.Project__c).add(pcr);
	}
}

for(datapm__OptinmOptin__c dtpm : [SELECT id,Project__c, Contact__c from datapm__OptinmOptin__c where Id IN :pcrs]){
	if(!datapmMap.containskey(dtpm.Contact__c + dtpm.Project__c)){
	     datapmMap.put(dtpm.Contact__c + dtpm.Project__c,new List<datapm__OptinmOptin__c>{dtpm});
	}
	else{
	    datapmMap.get(dtpm.Contact__c + dtpm.Project__c).add(dtpm);
	}
}

if(projectContactRoleMap.size() > 0){	
	for(string key : projectContactRoleMap.keyset()){
	    if(!datapmMap.containskey(key)){
	        UniquepcrList.addall(projectContactRoleMap.get(key));
	​    }
	}
}

All Answers

GhanshyamChoudhariGhanshyamChoudhari
List<String> conid = new List<string>();
List<String> proid = new List<string>();

List<datapm__OptinmOptin__c> dop = [select datapm__Contact__c,Project__c from datapm__OptinmOptin__c];
 
 for (datapm__OptinmOptin__c d: dop){
 conid.add(d.datapm__Contact__c);
 proid.add(d.Project__c );
}
 
 select Id, Project__c, Contact__c  from Project_Contact_Role__c where Id IN :pcrs and Contact__c NOT IN :conid and Project__c NOT IN :proid
Pradeep SinghPradeep Singh
Hi, you can do somthing like this. I haven't run the code, please let me know if it gives any error.
Map<string,List<Project_Contact_Role__c>> projectContactRoleMap = new Map<string,List<Project_Contact_Role__c>>();
Map<string,List<datapm__OptinmOptin__c>> datapmMap = new Map<string,List<datapm__OptinmOptin__c>>();
List<Project_Contact_Role__c> UniquepcrList = new List<Project_Contact_Role__c>();

for(Project_Contact_Role__c pcr : [SELECT id,Project__c, Contact__c from Project_Contact_Role__c where Id IN :pcrs]){
	if(!projectContactRoleMap.containskey(pcr.Contact__c + pcr.Project__c)){
	     projectContactRoleMap.put(pcr.Contact__c + pcr.Project__c,new List<Project_Contact_Role__c>{pcr});
	}
	else{
	    projectContactRoleMap.get(pcr.Contact__c + pcr.Project__c).add(pcr);
	}
}

for(datapm__OptinmOptin__c dtpm : [SELECT id,Project__c, Contact__c from datapm__OptinmOptin__c where Id IN :pcrs]){
	if(!datapmMap.containskey(dtpm.Contact__c + dtpm.Project__c)){
	     datapmMap.put(dtpm.Contact__c + dtpm.Project__c,new List<datapm__OptinmOptin__c>{dtpm});
	}
	else{
	    datapmMap.get(dtpm.Contact__c + dtpm.Project__c).add(dtpm);
	}
}

if(projectContactRoleMap.size() > 0){	
	for(string key : projectContactRoleMap.keyset()){
	    if(!datapmMap.containskey(key)){
	        UniquepcrList.addall(projectContactRoleMap.get(key));
	​    }
	}
}
This was selected as the best answer
Andy Kallio 7Andy Kallio 7

Thanks for the help Pradeep. Your code wasn't quite right, but it got me there.  Here is what it eventually turned into.

 

public Static void pcrRouter(List<Project_Contact_Role__c> projConRoles) {
        
        Set<Id> projIds = new Set<Id>();
        Map<string,List<Project_Contact_Role__c>> projectContactRoleMap = new Map<string,List<Project_Contact_Role__c>>();
        Map<string,List<datapm__OptinmOptin__c>> datapmMap = new Map<string,List<datapm__OptinmOptin__c>>();
        List<Project_Contact_Role__c> pcrsWOPermission = new List<Project_Contact_Role__c>();
        List<Project_Contact_Role__c> pcrsWPermission = new List<Project_Contact_Role__c>();    

        for(Project_Contact_Role__c pcr : projConRoles){

            projIds.add(pcr.Project__c);

            if(!projectContactRoleMap.containskey(string.valueOf(pcr.Contact__c) + string.valueOf(pcr.Project__c))){
                projectContactRoleMap.put(string.valueOf(pcr.Contact__c) + string.valueOf(pcr.Project__c),new List<Project_Contact_Role__c>{pcr});
            }
            else{
                projectContactRoleMap.get(string.valueOf(pcr.Contact__c) + string.valueOf(pcr.Project__c)).add(pcr);
            }
        }

        for(datapm__OptinmOptin__c dtpm : [SELECT Id, 
                                            datapm__Contact__c, 
                                            Project__c,
                                            datapm__OptInSource__r.datapm__DefaultMarketingPeriodMonths__c, 
                                            datapm__OptInSource__r.datapm__DefaultOptInLengthMonths__c,
                                            datapm__Status__c 
                                          from datapm__OptinmOptin__c   
                                          where Project__c IN :projIds]){
            if(!datapmMap.containskey(string.valueOf(dtpm.datapm__Contact__c) + string.valueOf(dtpm.Project__c))){
                datapmMap.put(string.valueOf(dtpm.datapm__Contact__c) + string.valueOf(dtpm.Project__c),new List<datapm__OptinmOptin__c>{dtpm});
            }
            else{
                datapmMap.get(string.valueOf(dtpm.datapm__Contact__c) + string.valueOf(dtpm.Project__c)).add(dtpm);
            }
        }

        if(projectContactRoleMap.size() > 0){   
            for(string key : projectContactRoleMap.keyset()){
                if(!datapmMap.containsKey(key)){
                    pcrsWOPermission.addAll(projectContactRoleMap.get(key));
                }
                if(datapmMap.containsKey(key)){
                    pcrsWPermission.addAll(projectContactRoleMap.get(key));
                }
            }
        }

        if(pcrsWOPermission != null || pcrsWOPermission.size() > 0) {
            system.debug('going to create permissions for contact without permission '+pcrsWOPermission);
            createPrivacyPermissions(pcrsWOPermission);
        }
        if(pcrsWPermission != null || pcrsWPermission.size() > 0) {
            system.debug('going to reinstate permissions for contact with permission '+pcrsWPermission);
            reinstatePrivacyPermissions(pcrsWPermission);
        }    

    }