0

Can anybody tell me how to iterate the Limit clause in Soql using Apex class?
I must have to retrieve the multiple numbers of records which are associated with user.
the User object has an integer field according to which I need to get the number of Applications.

User has a QC status field which is a percent field. I am calculating the number of applications which are need to be send to the QC queue and the rest of the Applications will go to the other queue. I am pasting the whole class code.

Since Limit clause only accepts integer so I am following the below approach which I think is wrong

 global void execute(Database.BatchableContext BC, List<User> userList) {

    string qStatement = 'Select Id,OwnerId,RecordTypeId,name,Username__c from Application_Review__c'; //For Select Statement
    string whereCondition; //For where clause

    Decimal noOfAppForQcRound; //Store the SUM of No of applications to be send.
    Decimal noOfAppForQc; //Round the noOfAppForQcRound value
    Integer myintval; //Convert decimal to Integer

    List<Application_Review__c> appReviewList = new List<Application_Review__c>(); //get List of Application Review Data w.r.t Username__c
    List<Application_Review__c> lstToUpdate = new List<Application_Review__c>(); //Store the List of Application Review data which has to be updated.
    List<Application_Review__c> lstToUpdatetoFinal = new List<Application_Review__c>(); //Store the List of Application Review data which has to be updated.
    List<Application_Review__c> lstOfAppRev = new List<Application_Review__c>(); //Retrieve final data to update

    Map<Id , Integer> mapOfnoOfAppForQc = new Map<Id , Integer>();
    Map<Id , List<Application_Review__c>> mapOfNoOfAppReview = new Map<Id , List<Application_Review__c>>();// Store the map of Application Review
    Map<Id , string> mapOfAppReview = new Map<Id , string>(); //store the Application Review Data
    Map<string,Id> mapOwnerId = new Map<string,Id>(); //to store the Queue id
    Map<Id , Decimal> mapOfQCPercent = new Map<Id , Decimal>();

    Set<Id> setOfUserId = new Set<Id>(); // store the userId in set
    Set<Id> setOfAppRevId = new Set<Id>(); // store the userId in set

    for(User u : userList){
        if(u.User_Role_Name__c == 'Reviewer'){
            system.debug('User List == '+u);
            setOfUserId.add(u.Id);
            mapOfQCPercent.put(u.Id , u.QC__c);
        }
    }
    List<Group> lstRevQueue = new List<Group>([select Id , DeveloperName 
                                               from Group]);

    for(Group revQueue : lstRevQueue){
        mapOwnerId.put(revQueue.DeveloperName, revQueue.Id);
    }

    appReviewList = [Select Id,Username__c, Name,OwnerId,Application_Status__c,QC_Status__c 
                     from Application_Review__c 
                     where Username__c =: setOfUserId and OwnerId= :mapOwnerId.get('Finish_Queue')];

    system.debug('appReviewList== '+appReviewList);
    system.debug('appReviewList size== '+appReviewList.size());

    for(Application_Review__c appRev : appReviewList){

        if(mapOfNoOfAppReview.containsKey(appRev.Username__c)){
            List<Application_Review__c> lstAppRev = mapOfNoOfAppReview.get(appRev.Username__c);
            lstAppRev.add(appRev);
            mapOfNoOfAppReview.put(appRev.Username__c,lstAppRev);
            system.debug('lstApp == '+lstAppRev);
            system.debug('lstApp size== '+lstAppRev.size());
        }else{
            mapOfNoOfAppReview.put(appRev.Username__c,new List<Application_Review__c> {appRev});
        }
    }

    for(User u : userList){
        if(u.User_Role_Name__c == 'Reviewer' && mapOfNoOfAppReview.containsKey(u.Id)){
            List<Application_Review__c> lstApp = mapOfNoOfAppReview.get(u.Id);
            noOfAppForQc = ((mapOfQCPercent.get(u.Id))/100)*lstApp.size();
            noOfAppForQcRound = Math.ceil(noOfAppForQc);
            myintval = Integer.valueOf(Math.round(noOfAppForQcRound));

            if(myintval > 0){
                mapOfnoOfAppForQc.put(u.Id , myintval);
            }
            system.debug('lstApp == '+lstApp);
            system.debug(' u='+u.Name);
            system.debug('lstApp size== '+lstApp.size());
            system.debug('myintval=='+myintval);
        }
    }
    Map<id,string> mapOfWhere = new Map<id,string>(); 
    /*This is where I need an advice*/
    /*how to apply the limit parameter from set or map which is being stored in mapOfnoOfAppForQc*/
    Map<Id, Integer> perUserCount = new Map<Id, Integer>()

 for(Application_Review__c appReview : [Select Id,Contact_Email__c,Austin_Energy_Account_Number__c,RecordTypeId,Username__c, Name,OwnerId,Application_Status__c,QC_Status__c from Application_Review__c where Username__c =: setOfUserId and OwnerId= :mapOwnerId.get('Finish_Queue')]){
 if (!perUserCount.containsKey(review.Username__c)) {
    perUserCount.put(review.Username__c, 0));
  }
    if (perUserCount.get(review.Username__c) <= mapOfnoOfAppForQc.get(review.Username__c)){
    system.debug('appReview send to final=='+appReview);
    Application_Review__c appRev = new Application_Review__c();
    appRev.Id = appReview.id;
    appRev.OwnerId =  mapOwnerId.get('QC_Queue');
    appRev.RecordTypeId = Schema.SObjectType.Application_Review__c.getRecordTypeInfosByDeveloperName().get('QC_Record').getRecordTypeId();
//EmailUtility.sendEmail(appReview);
    lstToUpdate.add(appRev);

    perUserCount.put(review.Username__c, perUserCount.get(review.Username__c) + 1);
}}


if(lstToUpdate.size() > 0){
        system.debug('lstToUpdate=='+lstToUpdate);
        update lstToUpdate;
    }

for(Application_Review__c appReview : [Select Id,Contact_Email__c,Austin_Energy_Account_Number__c,RecordTypeId,Username__c, Name,OwnerId,Application_Status__c,QC_Status__c from Application_Review__c where Username__c =: setOfUserId and OwnerId= :mapOwnerId.get('Finish_Queue')]){
        system.debug('appReview send to final=='+appReview);
        Application_Review__c appRev = new Application_Review__c();
        appRev.Id = appReview.id;
        appRev.OwnerId =  mapOwnerId.get('Final_Queue');
        appRev.RecordTypeId = Schema.SObjectType.Application_Review__c.getRecordTypeInfosByDeveloperName().get('Final_Record').getRecordTypeId();
        //EmailUtility.sendEmail(appReview);
        lstToUpdatetoFinal.add(appRev);
    }
    if(lstToUpdatetoFinal.size() > 0){
        system.debug('lstToUpdatetoFinal=='+lstToUpdatetoFinal);
        try{
            update lstToUpdatetoFinal;
        }catch(DmlException e){
            System.debug('The following exception has occurred: ' + e.getMessage());
        }

    }   
}   
3
  • 3
    Why do you need a LIMIT clause here at all, other than perhaps a catchall clause with a very high limit to avoid encountering governor limits? What does the integrer field on the user mean? Could you please edit your question to describe what problem you're trying to solve here? – David Reed May 12 '20 at 1:00
  • 1
    This code is running SOQL in a loop, which is generally a bad practice. – David Reed May 12 '20 at 1:00
  • I edited my question. – Areeba Irfan Ullah Khan May 12 '20 at 9:42
1

You need to adopt a bulkified solution to this issue, meaning that you run one SOQL query outside the loop. That's going to mean applying your limit in Apex, not in SOQL itself.

One way to approach this would be to store all of the user Ids in a Set<Id> userIds and run a single, static SOQL query like this:

List<Application_Review__c> = [
    SELECT Id, OwnerId, RecordTypeId, Name, Username__c 
    FROM Application_Review__c
    WHERE username__c IN :userIds AND OwnerId = :mapOwnerId.get('Finish_Queue')     
];

Note that the use of Apex binds dramatically simplifies your SOQL and obviates the need to manipulate queries as strings. This assumes that your data volume is moderate (i.e. this query won't return more than 50,000 rows).

Then, you can use a Map<Id, Integer> perUserCount to track how many reviews you've processed for each user, up to the maximum for that user, which you're already tracking in mapOfnoOfAppForQc.

for (Application_Review__c review : queryResults) {
    if (!perUserCount.containsKey(review.Username__c)) {
        perUserCount.put(review.Username__c, 0));
    }

    // Check if this user's quota has already been hit.
    if (perUserCount.get(review.Username__c) >= mapOfnoOfAppForQc.get(review.Username__c)) continue;

    // If not, increment their count.
    perUserCount.put(review.Username__c, perUserCount.get(review.Username__c) + 1);

    // Now, do all the updates you're already doing.
}
6
  • I understand what you are saying and I was following the same pattern as this: Application_Review__c appReview : [Select Id,Contact_Email__c,Austin_Energy_Account_Number__c,RecordTypeId,Username__c, Name,OwnerId,Application_Status__c,QC_Status__c from Application_Review__c where Username__c =: setOfUserId and OwnerId= :mapOwnerId.get('Finish_Queue') Limit :should be calculated according to Qc field] My question is how do I use the collection class for the limit parameter? – Areeba Irfan Ullah Khan May 12 '20 at 16:12
  • Areeba, the rest of my answer is explaining how you would apply your per-user limit in Apex, not in SOQL. – David Reed May 12 '20 at 16:13
  • I edited my question please check. – Areeba Irfan Ullah Khan May 12 '20 at 16:22
  • You edited your question in a way that makes my answer nonsensical. Did you read the entire answer? I laid out a pattern for how to do this implementation. – David Reed May 12 '20 at 16:23
  • now I got your point.. I edited please check if this is the thing you want me to do – Areeba Irfan Ullah Khan May 12 '20 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.