Iterate 'Limit n' clause in APEX

Question

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());
        }

    }   
}   

Answer 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.
}