0

I was querying the records in different methods based on different conditions but now I have been asked to merge all the queries in one query for efficiency purpose and use of wrapper class. I was trying to use aggregate queries using GROUP BY. however, it is not fulfilling my task. So, now I am trying to figure out how can I create a wrapper which will pass different variables in parent class. Current context to the code is-

@AuraEnabled(cacheable=true)
public static List<AggregateResult> getEverythingUsingGroubBy(){
    Integer openCount;
    List<AggregateResult> lstPartnerTask = new List<AggregateResult>();
    lstPartnerTask = [SELECT count(Id) recordCount, Status__c FROM Partner_Task__c GROUP BY Status__c ];
    system.debug(lstPartnerTask);

    for (AggregateResult ar : lstPartnerTask) {
        System.debug('Count of tasks are ' + ar.get('Status__c') +' is ' + ' -- '+  ar.get('recordCount'));
            
    }
        
    return lstPartnerTask;
}

It is returning result as -

(
    AggregateResult:{recordCount=3, Status__c=Unassigned}, 
    AggregateResult:{recordCount=5, Status__c=Assigned}, 
    AggregateResult:{recordCount=6, Status__c=In Progress}, 
    AggregateResult:{recordCount=3, Status__c=Completed}
)

Now to give more proper information-

I hv an object with field Status (picklist field- St1, St2, St3, St4) & Date field. Now, I want to hv -

  1. Count of records having St2
  2. Count of records having St1 + St2 +St3
  3. Count of records having Date < Today()
  4. Count of records having Date > Today & Date < Next 5 Days

I was able to achieve all this by running each query in individual method. But, now i have been asked to have one query and wrapper class to show result on UI of having all these 4 conditions. Aggregate query with GROUP BY is not working as per my current knowledge. So I am seeking some help.

UPDATED CODE-

Selector class's method which is returning List --

 public static List<Partner_Task__c> getAllCountTask(System.AccessLevel accessLevel){
    if(accessLevel == System.AccessLevel.USER_MODE) {
        List<Partner_Task__c> ptask= new List<Partner_Task__c>();
        ptask= [SELECT Name, Status__c, Due_Date__c from Partner_Task__c WITH USER_MODE];
        system.debug(ptask);
        return ptask;
        //return [SELECT Name, Status__c, Due_Date__c from Partner_Task__c WITH USER_MODE];
        
    }
    else {
        List<Partner_Task__c> ptask= new List<Partner_Task__c>();
        ptask= [SELECT Name, Status__c, Due_Date__c from Partner_Task__c];
        system.debug(ptask);
        return ptask;
        //return [SELECT Name, Status__c, Due_Date__c from Partner_Task__c];
    }
} 

Result returning as -

(Partner_Task__c:{Name=test Vamsi task 1, Status__c=Completed, Due_Date__c=2023-06-30 00:00:00, Id=a4RDM000001u2IJ2AY}, Partner_Task__c:{Name=test Vamsi task 2, Status__c=In Progress, Due_Date__c=2023-07-11 00:00:00, Id=a4RDM000001u2IO2AY}, Partner_Task__c:{Name=test Vamsi task 3, Status__c=Unassigned, Due_Date__c=2023-07-06 00:00:00, Id=a4RDM000001u2IP2AY}, Partner_Task__c:{Name=Test 2, Status__c=Unassigned, Due_Date__c=2023-07-15 00:00:00, Id=a4RDM000001u2KB2AY}

Calling this method in another class- where all logic should be reside and return value in different variables --

public static Integer getAllCountTask(){
    List<Partner_Task__c> ptask= new List<Partner_Task__c>();
    ptask = PartnerTaskSelector.getAllCountTask(System.AccessLevel.SYSTEM_MODE);
    Integer unassignedCount = PartnerTasks.getCountAllTaskRecord(ptask);
    system.debug(ptask);
    system.debug(unassignedCount);
    return unassignedCount;
}

public static List<PartnerTaskWrapper> getCoounnnttPartnerTask(){
    List<PartnerTaskWrapper> wrapList = new List<PartnerTaskWrapper>();
    List<Partner_Task__c> ptask = PartnerTaskSelector.getAllCountTask(System.AccessLevel.SYSTEM_MODE);
    system.debug('ptask '+ ptask);
    if(!ptask.isEmpty()){
        for(Partner_Task__c task : ptask){
            wrapList.add(new PartnerTaskWrapper(task));
        }
    }
    system.debug('WRAP '+wrapList);
    return wrapList;
} 


public class PartnerTaskWrapper {
    public Integer unassignedCount;
    public Integer openCount;
    public Integer pastDateCount;
    public Integer next7DaysCount;

    public PartnerTaskWrapper() {
        // Need to initialize the counts before we can increment them
        unassignedCount = 0;
        openCount = 0;
        pastDateCount = 0;
        next7DaysCount =0;
    }

    public Integer getCountAllTaskRecord(List<Partner_Task__c> ptask) {
        for(List<Partner_Task__c> st : ptask){
            if(st.Status__c == 'Unassigned'){
            unassignedCount++;
            system.debug('unassignedCount '+ unassignedCount);
            return unassignedCount;
        }
        else if(st.Status__c == 'In Progress' || st.Status__c == 'Assigned' || st.Status__c == 'Unassigned'){
            openCount++;
            system.debug('OPEN '+ openCount);
            return openCount;
        }
        else if(st.Due_Date__c < Today()){
            pastDateCount++;
            return pastDateCount;
        }
        else if(st.Due_Date__c > Today() && st.Due_Date__c < (Today()+7) ){
                next7DaysCount++;
            return next7DaysCount;
        }
            
        }

I have not applied logic yet to receive all variables. As I am new to wrapper so there are some mistakes and I am trying simple method as well to receive the data but getting errors as-

enter image description here

So I am able to complete it now. Uploading the current code. FINAL CODE -

public static List<PartnerTaskWrapper> getCountTask(){
    
    List<PartnerTaskWrapper> taskWrapper = new List<PartnerTaskWrapper>();
    Integer countedOpenTasks = 0;
    Integer countedUnassignedTasks =0 ;
    Integer countedOverdueTasks = 0;
    Integer counted7DaysDueTasks =0;
    List<Partner_Task__c> ptask= PartnerTaskSelector.getAllCountTask(System.AccessLevel.SYSTEM_MODE);
   
    for(Partner_Task__c allTask: ptask){
        if(allTask.Status__c == PartnerTaskConstants.STRING_ASSIGNED ||
          allTask.Status__c == PartnerTaskConstants.STRING_INPROGRESS || 
           allTask.Status__c == PartnerTaskConstants.STRING_UNASSIGNED){
            countedOpenTasks ++;
        }
        if(allTask.Status__c == PartnerTaskConstants.STRING_UNASSIGNED){
            countedUnassignedTasks ++;
        }
        if(allTask.Overdue_Tasks__c && allTask.Status__c != PartnerTaskConstants.STRING_COMPLETED){
            countedOverdueTasks ++;
        }
        if(allTask.Task_Due_within_7_days__c ){
            counted7DaysDueTasks ++;
        }
    }
    taskWrapper.add(new PartnerTaskWrapper(countedOpenTasks,countedUnassignedTasks, countedOverdueTasks, counted7DaysDueTasks ));
    system.debug('days7dueCount '+counted7DaysDueTasks);
    system.debug('overdue '+countedOverdueTasks);
    system.debug('unassigned '+countedUnassignedTasks);
    system.debug('openCount '+countedOpenTasks);
    system.debug('wrapper '+ taskWrapper);
      return taskWrapper;
    }

//Wrapper Class for Count Partner Tasks for All Task Home Page Component

public class PartnerTaskWrapper{
    @AuraEnabled public Integer countedOpenTasks = 0;
    @AuraEnabled public Integer countedUnassignedTasks =0 ;
    @AuraEnabled public Integer countedOverdueTasks = 0;
    @AuraEnabled public Integer counted7DaysDueTasks =0;

    public PartnerTaskWrapper(Integer countedOpenTasks,Integer countedUnassignedTasks, Integer countedOverdueTasks, Integer counted7DaysDueTasks){
        
        this.countedOpenTasks =countedOpenTasks;
        this.countedUnassignedTasks= countedUnassignedTasks;
        this.countedOverdueTasks = countedOverdueTasks;
        this.counted7DaysDueTasks = counted7DaysDueTasks;
    }
    
    
}
4
  • 2
    This sounds like you're veering into X-Y problem territory. That is, "storing these record counts in different variables" and "performing addition on them" is your proposed solution to a problem. Instead of focusing on the solution, you should tell us about the overall problem that you're trying to solve. It could very well be that you're overcomplicating things. If you're trying to get the count of all records here, for example, this could be as simple as using GROUP BY ROLLUP instead of the plain 'ol GROUP BY.
    – Derek F
    Jul 11, 2023 at 13:19
  • @DerekF I will try to give you more insight here. I hv an object with field Status (picklist field- St1, St2, St3, St4) & Date field. Now, I want to hv - 1. Count of records having St2 2. Count of records having St1 + St2 +St3 3. Count of records having Date < Today() 4. Count of records having Date > Today & Date < Next 5 Days I was able to achieve all this by running each query in individual method. But, now i have been asked to have one query and wrapper class to show result on UI of all these 4 conditions. I hope i had able to give you proper idea, if not please ask anything.
    – SF Learner
    Jul 11, 2023 at 13:31
  • Please edit your question to add that information.
    – Derek F
    Jul 11, 2023 at 13:32
  • done @DerekF. I have added the info.
    – SF Learner
    Jul 11, 2023 at 13:38

1 Answer 1

0

When you start needing to put data into multiple buckets that aren't refinements of your search criteria (an example of a "refinement" being something like "count of each status used by date"), then SOQL isn't really going to be able to handle that on its own. Apex (or perhaps Javascript if you're working in an LWC) is going to be involved.

In its most basic form, you might have your wrapper class include an addRecord() method that takes a single record, and tests for your various conditions

// This is meant to be a partial example
public class Wrapper {
    public Integer status2Count;
    public Integer status1Or2Or3Count;
    public Integer pastDateCount;

    public Wrapper() {
        // Need to initialize the counts before we can increment them
        status2Count = 0;
        status1Or2Or3Count = 0;
        pastDateCount = 0;
    }

    public void addRecord(Partner_Task__c input) {
        if(input.Status__c == 'Status 2'){
            status2Count++;
        }

        // Note that these are independent IFs rather than IF-ELSE IF-ELSE
        //   because your conditions are not mutually exclusive
        if(input.Status__c == 'Status 1' || input.Status__c == 'Status 2' || input.Status__c == 'Status 3'){
            status1Or2Or3Count++;
        }

        if(input.Date__c < Date.today()){
            pastDateCount++;
        }
    }
}

You could also pretty easily turn those conditions into formula fields on your SObject, which would reduce the amount of work that you need to do in code. I.e.
if(input.Status__c == 'Status 1' || input.Status__c == 'Status 2' || input.Status__c == 'Status 3'){
could be reduced to
if(input.Status1Or2Or3_Formula__c){

1
  • Thanks, so much @Derek F . With your help I was able to resolve it completely.
    – SF Learner
    Jul 12, 2023 at 16:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .