Merge multiple List from queries

Question

I would like to merge multiple List into one but I am not sure on how to do it .. My code is the following:

public with sharing class ApprovalDashboard {
    public ApprovalDashboard(){
        
    }
    
    @AuraEnabled(cacheable=true)
    public static List<Object> approvalTableData(){
        List<Object> FinalList = new List<Object>();
        List<AggregateResult> OppList = [SELECT Opportunity__r.Id  FROM Approval__c 
                                     WHERE (Type__c = 'Investment_Committee' OR Type__c = 'Pre_IC')
                                     AND Status__c != Null
                                     AND CALENDAR_YEAR(Date__c) = 2021
                                     GROUP BY Opportunity__r.Id];
        
        for(AggregateResult item: OppList){
            List<Opportunity> OppData = [SELECT Id, Name, StageName
                                         FROM Opportunity
                                         WHERE Id = :item.Id];
            
            List<Approval__c>ApprovalList = [SELECT Name, date__c FROM Approval__c WHERE Opportunity__r.id = :item.Id];
            system.debug(OppData);
        }
        
        
        
        
        return OppList;
        
    }

I would like to mege OppData and ApprovalList into one big List in the format [{}, {},{}] .. Any help ?

Answer 1

You could merge the 2 lists with code like below outside of the for loop:

List<SObject> FinalList = new List<SObject>(); 
FinalList.addAll(OppData);
FinalList.addAll(ApprovalList);

Also, check out similar thread here

Answer 2

An another answer to the question is the wrapper class.

But, more important to say, it is very BAD practice to do SOQL query in the for loop. You should avoid it anytime, anywhere and anything!

And, in your case, it might not need to merge the 2 lists. Why not using the relationship query to do the same thing?

It is a re-writing and try to do the same thing easier and much more safe.

List<Approval__c> ApprovalList = [SELECT Name, data__c, Status__c, Date__c, 
                                         Opportunity__c, Opportunity__r.Name, Opportunity__r.StageName 
                                    FROM Approval__c 
                                   WHERE (Type__c = 'Investment_Committee' OR Type__c = 'Pre_IC')
                                         AND Status__c != Null
                                         AND CALENDAR_YEAR(Date__c) = 2021 ];
    
return ApprovalList;

Answer 3

The wrapper to send Opportunity and its associated is one Item and you will need to build the list of this Item to send as multiple Items.

something like an array of objects which contains two items each - Oppty and associated approval process. Pseudo and non-tested code below. Additionally added a comment for the other issues in your code

1. query inside for loops
2. repetitive queries

        public with sharing class ApprovalDashboard {
        public ApprovalDashboard(){
    
       }
    
       @AuraEnabled(cacheable=true)
       public static List<ApprovalData> approvalTableData(){
        Set<Id> setOpptyId=new Set<Id>();
        List<ApprovalData> listAppData=new List<ApprovalData>();
        List<Object> FinalList = new List<Object>();
        List<AggregateResult> OppList = [SELECT Opportunity__r.Id FROM Approval__c
                                            WHERE (Type__c = 'Investment_Committee' OR Type__c = 'Pre_IC')
                                            AND Status__c != Null
                                            AND CALENDAR_YEAR(Date__c) = 2021
                                                                        GROUP BY Opportunity__r.Id];
    
    
        for(AggregateResult item: OppList) {
            setOpptyId.add (item.get('Opportunity__r.Id');
            // query in a loop, not best prctises and will have issues
            // I dont think this will work as AggregateResult does not retiurn id, it returns bsed on your query
            // List<Opportunity> OppData = [SELECT Id, Name, StageName
            //                              FROM Opportunity
            //                              WHERE Id = :item.Id];
            
            // List<Approval__c>ApprovalList = [SELECT Name, date__c FROM Approval__c WHERE Opportunity__r.id = :item.Id];
            system.debug(OppData);
        }
    
        for(Opportunity oppty = [SELECT Id,Name,(SELECT Name, date__c FROM Approvals__r WHERE Opportunity__r.id in :setOpptyId) FROM Opportunity WHERE Id in :setOpptyId]){
            ApprovalData appData= new ApprovalData();
            appData.oppty=oppty;
            appData.approval=oppty.Approvals__r;
            listAppData.add(appData);
        }        
        return listAppData;
        
        }
    
        Public class ApprovalData {
            public Opportunity oppoty {get;set;}
            //technically one oppty can have multiple approval
            public  List<Approval__c> approval {get;set;}
        }

Updates: Key things to note when deciding the structure for wrappers

1. Identify the structure required for the purpose of requirement Added more explanation and sample code (non tested)
2. will there be a reason in the future that the data required by logic is a little too generic and changes frequently? if yes we may not use exact data type but a generic object and processing party can consume as they wont

Best practises

1. Try to create a flat structure with key-value pairs type of data.
2. Handle data conversion if make sense, at the server-side. for ex- timezone, related things as data set to know the user context and actual timezones etc
3. Generic and reusable wrapper structure

Sample code for the problem statement

// This stricture is generic enough to handle any type of data and not need to change the wrapper if there is a need to add more attributes in the wrapper
// This gives the flexibility to have data from any unrelated tables and give meaningful name as required
// Con is to have a little more parsing logic for consumer
// slightly complicated stricture to add a data set 
List<Data
Public class Data {
    public String name{get; set;}
    //technically one oppty can have multiple approvals
    public String value {get; set;}
    public String type {get; set;}
}
// data returned would look like this
// Apex return List<ApprovalData>
public Class ApprovalData{
    List<Data> dataList=new List<Data>();
    String key; //to identify record, may be oppty id
}
// Uisng This you Return list of this and each item has all attributed need by consumer
// Straight forward stricture, easy to build and add data to
// Easyy to consume
// COn - tighlty coupled with data set and would need addtional attributes when datatable strucutre changes
Public class Data {
    public String OpportunityName{get; set;}
    //technically one oppty can have multiple approval
    public String OpportunityStatus {get; set;}
    public String LastApprovalDate {get; set;}
    public String LastApprovalstatus {get; set;}
}
// data returned would look like this
// Apex return List<Data>, flat structure as you need, easy