You need to sign in to do that
Don't have an account?
Mark Developer
Avoiding SOQL in for loop?
Hi Can any one help me in avoiding the SOQL in for loop in the below code. I am not a good developer though. Any help is much appreciated.
public class Class1{
public static final Integer SOQL_QUERY_LIMIT = 100;
public static final String SHARINGLEVEL = 'Edit';//All,Edit,Read
public static final String SHARE = '__Share';
public static void beforeInsertCreateObjectSharing(List<CMPLDEV2_Record_Access_Rule_c> triggerNewList){
Set<String> querySet = new Set<String>();
String query = '';
String sharingObjectName = '';
if(SOQL_QUERY_LIMIT <= Limits.getQueries() + triggerNewList.size()){
triggerNewList[0].addError('Exceeding query limit.');
}
Map<String,List<SObject>> mapOfSobject = new Map<String,List<SObject>>();
for(CMPLDEV2_Record_Access_Rule_c each : triggerNewList){
if(each.CMPLDEV2_Active_of_the_rulec && !String.isBlank(each.CMPLDEV2Target_Object_c)){
query = '';
query = 'SELECT ID,OwnerId,(SELECT ID,' + each.CMPLDEV2_User_Field_Namec + ' FROM ' + each.CMPLDEV2API_Path_to_User_Relationshipc + ') FROM ' + each.CMPLDEV2Target_Object_c;
//System.debug('** QUERY ** '+ query);
List<SObject> listSobject = Database.query(query);
//System.debug('** listSobject ** '+ JSON.serialize(listSobject));
//System.debug('*each.CMPLDEV2_Target_Objectc** '+each.CMPLDEV2*** '+each.CMPLDEV2_Target_Object_c);
//if Custom Object
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
sharingObjectName = each.CMPLDEV2_Target_Objectc.replace('_c',SHARE);
}
//if standard Object
else{
sharingObjectName = each.CMPLDEV2_Target_Object_c + 'Share';
}
System.debug('**Sharing Object* '+sharingObjectName);
for(SObject eachSObject : listSobject){
/System.debug('*Each Sobject** ' + eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c));
System.debug('**Each* ' + eachSObject);
System.debug('**Each* ' + each.CMPLDEV2_API_Path_to_User_Relationship_c); */
if(eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c) != NULL){
for(SObject eachUser : eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c)){
System.debug('**User Id** ' + eachUser.get('Id'));
Schema.SObjectType sObjectType = Schema.getGlobalDescribe().get(sharingObjectName);
if(sObjectType != NULL && eachSObject.get('OwnerId') != eachUser.get(each.CMPLDEV2_User_Field_Name_c)){
SObject sObjectSharing = sObjectType.newSObject();
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
sObjectSharing.put('ParentId',eachSObject.Id);
sObjectSharing.put('AccessLevel',SHARINGLEVEL );
}
else{
sObjectSharing.put(each.CMPLDEV2_Target_Object_c + 'Id' , eachSObject.Id);
sObjectSharing.put(each.CMPLDEV2_Target_Object_c + 'AccessLevel' , SHARINGLEVEL );
}
sObjectSharing.put('UserOrGroupId',eachUser.get(each.CMPLDEV2_User_Field_Name_c));
if(mapOfSobject.containsKey(sharingObjectName)){
mapOfSobject.get(sharingObjectName).add(sObjectSharing);
}
else{
List<SObject> sObjectList = new List<SObject>();
sObjectList.add(sObjectSharing);
mapOfSobject.put(sharingObjectName, sObjectList);
}
}
else{
System.debug('SObject is NULL '+sharingObjectName);
}
}
}
}
//querySet.add(query);
//select Id, ContactId, UserOrGroupId, ContactAccessLevel from ContactShare where UserOrGroupId NOT IN (Select OWNERID from Contact)
// see if trigger exists yet, if not, create it
String relName = each.CMPLDEV2_API_Path_to_User_Relationship_c;
System.debug(relName);
Schema.SObjectType targetObjectName = Schema.getGlobalDescribe().get(each.CMPLDEV2_Target_Object_c);
System.debug(targetObjectName);
for (ChildRelationship rel : targetObjectName.getDescribe().getChildRelationships())
{
System.debug(rel.getRelationshipName());
String relationshipName = rel.getRelationshipName();
if(relationshipName != null)
{
if(RecordSharingUtility.trimNameSpace(relationshipName).equalsIgnoreCase(relName))
{
SObjectType childType = rel.getChildSObject();
String name = childType.getDescribe().getName();
triggerCreation(name, UserInfo.getSessionId());
break;
}
}
}
}
}
// System.debug('* mapOfSobject * '+ mapOfSobject);
for(String eachSobject : mapOfSobject.keySet()){
List<Database.SaveResult> saveResult = Database.insert(mapOfSobject.get(eachSobject),false);
System.debug('saveResult*** '+saveResult);
//insert mapOfSobject.get(eachSobject);
}
}
@future(callout=true)
public static void triggerCreation(String targetObject, String sessionId){
System.debug('Trigger Creation for: ' + targetObject);
Boolean isTriggerCreated = [Select count() from ApexTrigger where Status='Active' AND TableEnumOrId =: targetObject AND Name Like '%_AuditTrigger'] > 0 ? true : false;
System.debug('Trigger exists? ' + isTriggerCreated);
if(!isTriggerCreated){
//Create one trigger automatic for this object
RecordSharingUtility.createAutoTrigger(targetObject, sessionId);
}
}
/* public static void beforeUpdateObjectSharing(List<CMPLDEV2_Record_Access_Rulec> triggerNew,List<CMPLDEV2Record_Access_Rulec> triggerOld,Map<Id,CMPLDEV2Record_Access_Rulec> triggerNewMap,Map<Id,CMPLDEV2Record_Access_Rule_c> triggerOldMap){
List<SObject> reactivtingRules = new List<SObject>();
List<SObject> deactivtingRules = new List<SObject>();
for(CMPLDEV2_Record_Access_Rule_c each : triggerNew){
if(each.CMPLDEV2_Active_of_the_rulec != triggerOldMap.get(each.Id).CMPLDEV2Active_of_the_rule_c){
//Activating deactivate ruls
if(each.CMPLDEV2_Active_of_the_rule_c){
reactivtingRules.add(each);
}
else{
//activtingRules
deactivtingRules.add(each);
}
}
}
if(!reactivtingRules.isEmpty()){
beforeInsertCreateObjectSharing(reactivtingRules);
}
else if(!deactivtingRules.isEmpty()){
DacObjShar(deactivtingRules);
}
}
*/
/*public static void DacObjShar(List<CMPLDEV2_Record_Access_Rule_c> triggerNew){
String query = '';
for(CMPLDEV2_Record_Access_Rule_c each : triggerNew){
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
String sharingObjectName = each.CMPLDEV2_Target_Objectc.replace('_c',SHARE);
query = 'Select Id, ParentId, UserOrGroupId, AccessLevel From ' + sharingObjectName + ' where UserOrGroupId NOT IN (Select OWNERID from ' + each.CMPLDEV2_Target_Object_c + ')';
}
else{
query = 'Select Id, ' + each.CMPLDEV2_Target_Objectc + 'Id, UserOrGroupId,'+ each.CMPLDEV2Target_Objectc + 'AccessLevel from ' + each.CMPLDEV2Target_Objectc + 'Share where UserOrGroupId NOT IN (Select OWNERID from ' + each.CMPLDEV2Target_Object_c + ')';
}
System.debug('Query '+query);
List<SObject> listSobject = Database.query(query);
if(!listSobject.isEmpty()){
System.debug('Delete listSobject '+listSobject);
List<Database.DeleteResult> saveResult = Database.delete(listSobject,false);
System.debug('Delete listSobject '+saveResult);
}
}
}
*/
/*public static void deleteObjectSharing(List<Sobject> triggerOld){
DacObjShar(triggerOld);
}
*/
}
public class Class1{
public static final Integer SOQL_QUERY_LIMIT = 100;
public static final String SHARINGLEVEL = 'Edit';//All,Edit,Read
public static final String SHARE = '__Share';
public static void beforeInsertCreateObjectSharing(List<CMPLDEV2_Record_Access_Rule_c> triggerNewList){
Set<String> querySet = new Set<String>();
String query = '';
String sharingObjectName = '';
if(SOQL_QUERY_LIMIT <= Limits.getQueries() + triggerNewList.size()){
triggerNewList[0].addError('Exceeding query limit.');
}
Map<String,List<SObject>> mapOfSobject = new Map<String,List<SObject>>();
for(CMPLDEV2_Record_Access_Rule_c each : triggerNewList){
if(each.CMPLDEV2_Active_of_the_rulec && !String.isBlank(each.CMPLDEV2Target_Object_c)){
query = '';
query = 'SELECT ID,OwnerId,(SELECT ID,' + each.CMPLDEV2_User_Field_Namec + ' FROM ' + each.CMPLDEV2API_Path_to_User_Relationshipc + ') FROM ' + each.CMPLDEV2Target_Object_c;
//System.debug('** QUERY ** '+ query);
List<SObject> listSobject = Database.query(query);
//System.debug('** listSobject ** '+ JSON.serialize(listSobject));
//System.debug('*each.CMPLDEV2_Target_Objectc** '+each.CMPLDEV2*** '+each.CMPLDEV2_Target_Object_c);
//if Custom Object
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
sharingObjectName = each.CMPLDEV2_Target_Objectc.replace('_c',SHARE);
}
//if standard Object
else{
sharingObjectName = each.CMPLDEV2_Target_Object_c + 'Share';
}
System.debug('**Sharing Object* '+sharingObjectName);
for(SObject eachSObject : listSobject){
/System.debug('*Each Sobject** ' + eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c));
System.debug('**Each* ' + eachSObject);
System.debug('**Each* ' + each.CMPLDEV2_API_Path_to_User_Relationship_c); */
if(eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c) != NULL){
for(SObject eachUser : eachSObject.getSobjects(each.CMPLDEV2_API_Path_to_User_Relationship_c)){
System.debug('**User Id** ' + eachUser.get('Id'));
Schema.SObjectType sObjectType = Schema.getGlobalDescribe().get(sharingObjectName);
if(sObjectType != NULL && eachSObject.get('OwnerId') != eachUser.get(each.CMPLDEV2_User_Field_Name_c)){
SObject sObjectSharing = sObjectType.newSObject();
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
sObjectSharing.put('ParentId',eachSObject.Id);
sObjectSharing.put('AccessLevel',SHARINGLEVEL );
}
else{
sObjectSharing.put(each.CMPLDEV2_Target_Object_c + 'Id' , eachSObject.Id);
sObjectSharing.put(each.CMPLDEV2_Target_Object_c + 'AccessLevel' , SHARINGLEVEL );
}
sObjectSharing.put('UserOrGroupId',eachUser.get(each.CMPLDEV2_User_Field_Name_c));
if(mapOfSobject.containsKey(sharingObjectName)){
mapOfSobject.get(sharingObjectName).add(sObjectSharing);
}
else{
List<SObject> sObjectList = new List<SObject>();
sObjectList.add(sObjectSharing);
mapOfSobject.put(sharingObjectName, sObjectList);
}
}
else{
System.debug('SObject is NULL '+sharingObjectName);
}
}
}
}
//querySet.add(query);
//select Id, ContactId, UserOrGroupId, ContactAccessLevel from ContactShare where UserOrGroupId NOT IN (Select OWNERID from Contact)
// see if trigger exists yet, if not, create it
String relName = each.CMPLDEV2_API_Path_to_User_Relationship_c;
System.debug(relName);
Schema.SObjectType targetObjectName = Schema.getGlobalDescribe().get(each.CMPLDEV2_Target_Object_c);
System.debug(targetObjectName);
for (ChildRelationship rel : targetObjectName.getDescribe().getChildRelationships())
{
System.debug(rel.getRelationshipName());
String relationshipName = rel.getRelationshipName();
if(relationshipName != null)
{
if(RecordSharingUtility.trimNameSpace(relationshipName).equalsIgnoreCase(relName))
{
SObjectType childType = rel.getChildSObject();
String name = childType.getDescribe().getName();
triggerCreation(name, UserInfo.getSessionId());
break;
}
}
}
}
}
// System.debug('* mapOfSobject * '+ mapOfSobject);
for(String eachSobject : mapOfSobject.keySet()){
List<Database.SaveResult> saveResult = Database.insert(mapOfSobject.get(eachSobject),false);
System.debug('saveResult*** '+saveResult);
//insert mapOfSobject.get(eachSobject);
}
}
@future(callout=true)
public static void triggerCreation(String targetObject, String sessionId){
System.debug('Trigger Creation for: ' + targetObject);
Boolean isTriggerCreated = [Select count() from ApexTrigger where Status='Active' AND TableEnumOrId =: targetObject AND Name Like '%_AuditTrigger'] > 0 ? true : false;
System.debug('Trigger exists? ' + isTriggerCreated);
if(!isTriggerCreated){
//Create one trigger automatic for this object
RecordSharingUtility.createAutoTrigger(targetObject, sessionId);
}
}
/* public static void beforeUpdateObjectSharing(List<CMPLDEV2_Record_Access_Rulec> triggerNew,List<CMPLDEV2Record_Access_Rulec> triggerOld,Map<Id,CMPLDEV2Record_Access_Rulec> triggerNewMap,Map<Id,CMPLDEV2Record_Access_Rule_c> triggerOldMap){
List<SObject> reactivtingRules = new List<SObject>();
List<SObject> deactivtingRules = new List<SObject>();
for(CMPLDEV2_Record_Access_Rule_c each : triggerNew){
if(each.CMPLDEV2_Active_of_the_rulec != triggerOldMap.get(each.Id).CMPLDEV2Active_of_the_rule_c){
//Activating deactivate ruls
if(each.CMPLDEV2_Active_of_the_rule_c){
reactivtingRules.add(each);
}
else{
//activtingRules
deactivtingRules.add(each);
}
}
}
if(!reactivtingRules.isEmpty()){
beforeInsertCreateObjectSharing(reactivtingRules);
}
else if(!deactivtingRules.isEmpty()){
DacObjShar(deactivtingRules);
}
}
*/
/*public static void DacObjShar(List<CMPLDEV2_Record_Access_Rule_c> triggerNew){
String query = '';
for(CMPLDEV2_Record_Access_Rule_c each : triggerNew){
if(each.CMPLDEV2_Target_Objectc.toLowerCase().contains('_c')){
String sharingObjectName = each.CMPLDEV2_Target_Objectc.replace('_c',SHARE);
query = 'Select Id, ParentId, UserOrGroupId, AccessLevel From ' + sharingObjectName + ' where UserOrGroupId NOT IN (Select OWNERID from ' + each.CMPLDEV2_Target_Object_c + ')';
}
else{
query = 'Select Id, ' + each.CMPLDEV2_Target_Objectc + 'Id, UserOrGroupId,'+ each.CMPLDEV2Target_Objectc + 'AccessLevel from ' + each.CMPLDEV2Target_Objectc + 'Share where UserOrGroupId NOT IN (Select OWNERID from ' + each.CMPLDEV2Target_Object_c + ')';
}
System.debug('Query '+query);
List<SObject> listSobject = Database.query(query);
if(!listSobject.isEmpty()){
System.debug('Delete listSobject '+listSobject);
List<Database.DeleteResult> saveResult = Database.delete(listSobject,false);
System.debug('Delete listSobject '+saveResult);
}
}
}
*/
/*public static void deleteObjectSharing(List<Sobject> triggerOld){
DacObjShar(triggerOld);
}
*/
}