+ Start a Discussion
RyanSchierholzRyanSchierholz 

Combine three custom objects into a nested array to display on a Lightning web component

The ultimate goal is to be able to iterate over an array in a Lightning web component to show internal reviews about our vendors. There would be at least three levels: Super Category, Category, Review (maybe even four: Super Category, Category, Vendor, Review), so that the root lists are not repeated in the display. For example:

Super Category 1
-Category A
--Review 00
--Review 01
-Category B
--Review 02

Super Category 2
-Category C
--Review 03

Super Category is Parent over Category, which is parent over Review. Review also has references to Super Category. 

ultimately the array might look like this:
superCategories = [
        {
            Name: 'Additions',
            Categories: [
                {
                    Name: 'Bathroom Additions',
                    Reviews: [
                        {
                            Vendor: 'Jay at Pritches Closets',
                            Rating: 5,
                            Comment: 'this is the comment',
                            Phone: '8005551234'
                        }
                    ]
                },
                {
                    Name: 'Kitchen Additions',
                    Reviews: [
                        {
                            Vendor: 'Phil Dunphy',
                            Rating: 4,
                            Comment: 'this is the comment',
                            Phone: '8005551234'
                        }
                    ]
                }
            ]
        }
    ];

What is the best way to accomplish this? In the Controller or the .js of the component? And how?
Best Answer chosen by RyanSchierholz
Nayana KNayana K
1. Only show the Super Categories and Categories in which there are reviews (of vendors) 
    -- Assuming we need only super categories having atleast one Categorys.Reviews
2. If there are more than one review for a Vendor in a category, only show the Vendor/Review once, but show the number of 'duplicates'. 
    - Group by Review.Vendor__c
3. Ability to filter through search Ryan Schierholz
    - I assume, if searchTerm is passed to the fetchData(), then user want to search. If empty string is passed, we display all super categories without filter. 
 
public class CategoriesController {
	@AuraEnabled
	public static List<SuperCategory> fetchData(String searchTerm) {
	
		SuperCategory tempSC;
		Category tempCategory;
		Review tempReview;
		
		Map<Id, SuperCategory> mapSuperCategory = new Map<Id, SuperCategory>();
		Map<String, Review> mapVendorToReview;
		String query = '';
		query += 'SELECT Id, Name, Super_Category__c, Super_Category__r.Name, (SELECT Id, Vendor__c, Rating__c, Comment__c, Phone__c FROM Reviews__r';
		if(String.isNotBlank()) {
			query += ' WHERE Vendor__c =: searchTerm';
		}
		query += ') FROM PVL_Category__c WHERE Super_Category__c != NULL ';
		
		
		for(PVL_Category__c objCategory : Database.query(query)) {
			// if atleast one review
			if(!objCategory.Reviews__r.isEmpty()) {
				tempSC = new SuperCategory();
				tempCategory = new Category();
				
				if(mapSuperCategory.containsKey(objCategory.Super_Category__c)) {
					tempSC = mapSuperCategory.get(objCategory.Super_Category__c);
					
				}
				else {
					tempSC = new SuperCategory(objCategory.Super_Category__c, objCategory.Super_Category__r.Name);
					mapSuperCategory.put(objCategory.Super_Category__c, tempSC);
				}
				
				tempCategory = new Category(objCategory.Id, objCategory.Name);
				mapVendorToReview = new Map<String, Review>();
				
				for(Review__c objReview : objCategory.Reviews__r)) {
					if(mapVendorToReview.containsKey(objReview.Vendor__c)) {
						mapVendorToReview.get(objReview.Vendor__c).dupeCount++;
					}
					else {
						mapVendorToReview.put(objReview.Vendor__c, new Review(objReview.Id, objReview.Vendor__c, objReview.Rating__c != null ? Integer.valueOf(objReview.Rating__c) : null, 
																				objReview.Comment__c, objReview.Phone__c, 1));
					}
				}
				tempCategory.Reviews.addAll(mapVendorToReview.values());
				tempSC.Categories.add(tempCategory);
			}
		}
		
		return mapSuperCategory.values();
	}
	
	public class SuperCategory {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled 
		public List<Category> Categories {get;set;}
		
		public SuperCategory(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Categories = new List<Category>();
		}
	}
	
	public class Category {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled
		public List<Review> Reviews {get;set;}
		
		public Category(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Reviews = new List<Review>();
		}
	}
	
	public class Review {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled
		public String Vendor {get;set;}
		@AuraEnabled
		public Integer Rating {get;set;}
		@AuraEnabled
		public String Comment {get;set;}
		@AuraEnabled
		public String Phone {get;set;}
		@AuraEnabled 
		public Integer dupeCount {get;set;}
		
		public Review(Id Id, String Vendor, Integer Rating, String Comment, String Phone, Integer dupeCount) {
			this.Id = Id;
			this.Vendor = Vendor;
			this.Rating = Rating;
			this.Comment = Comment;
			this.Phone = Phone;
			this.dupeCount = dupeCount;
		}
	}
}

Please mark this as solved if it helped you.

All Answers

Nayana KNayana K
Assumptions:
1. Object Api Names
- Super_Category__c
- Category__c
- Review__c

2. Reference Field names
- FieldName: Category__c.Super_Category__c | Rlationship name: Category.Super_Category__r
- FieldName: Review__c.Category__c | Rlationship name: Review__c.Category__r

3. You have apex method and wrapper class like below:
public class CategoriesController {
	@AuraEnabled
	public static List<SuperCategory> fetchData() {
		Map<Id, Category__c> mapCategory = new Map<Id, Category__c>();
		Set<Id> setCategoryIds = new Set<Id>();
		List<SuperCategory> lstSuperCategory = new List<SuperCategory>();
		SuperCategory tempSC;
		for(Super_Category__c objSuperCategory : [SELECT Id, Name, (SELECT Id, Name FROM Category__r) FROM Super_Category__c]) {
			tempSC = new SuperCategory(objSuperCategory.Id, objSuperCategory.Name);
			
			for(Category__c objCategory : objSuperCategory.Category__r) {
				
				tempSC.Categories.add(new Category(objCategory.Id, objCategory.Name));
				setCategoryIds.add(objCategory.Id);
			}
			lstSuperCategory.add(tempSC);
		}
		
		if(!setCategoryIds.isEmpty()){
			mapCategory = [SELECT Id, (SELECT Id, Vendor__c, Rating__c, Comment__c, Phone__c FROM Review__r) FROM Category__c WHERE Id IN: setCategoryIds.keySet()];
			for(SuperCategory objSCWrap : lstSuperCategory) {
				for(Category objCategoryWrap : objSCWrap.Categories) {
					for(Review__c objReview : mapCategory.get(objCategoryWrap.Id).Review__r)) {
						objCategoryWrap.Reviews.add(new Review(objReview.Id, objReview.Vendor__c, objReview.Rating__c != null ? Integer.valueOf(objReview.Rating__c) : null, 
							objReview.Comment__c, objReview.Phone__c));
					}
				}
			}
		}
		return lstSuperCategory;
	}
	
	public class SuperCategory {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled 
		public List<Category> Categories {get;set;}
		
		public SuperCategory(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Categories = new List<Category>();
		}
	}
	
	public class Category {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled
		public List<Review> Reviews {get;set;}
		
		public Category(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Reviews = new List<Review>();
		}
	}
	
	public class Review {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled
		public String Vendor {get;set;}
		@AuraEnabled
		public Integer Rating {get;set;}
		@AuraEnabled
		public String Comment {get;set;}
		@AuraEnabled
		public String Phone {get;set;}
		
		public Review(Id Id, String Vendor, Integer Rating, String Comment, String Phone) {
			this.Id = Id;
			this.Vendor = Vendor;
			this.Rating = Rating;
			this.Comment = Comment;
			this.Phone = Phone;
		}
	}
}

4. Call this apex CategoriesController.fetchData() from lwc and store the response in var and console it. You should get the same structure (Only extra thing is that I have kept Id in every wrapper instance)

Please let me know if you face any issues. Please correct if any typos.
RyanSchierholzRyanSchierholz
Oh wow! What a treat. Thank you for working on that and offering more help if I have issues. I adjusted it for the names in my system, but your assumptions were really good! Some of the relationship names had to change (Reviews__r instead of Review__r). With all the adjustments I am confident making, I got the issues down to one:
User-added image

I tried removing keySet(), but then got a different error:

User-added image
Anymore insight you have is VERY much appreciated. Thank you, Nayana.
Nayana KNayana K
mapCategory = new Map<Id, Category__c>([SELECT Id, (SELECT Id, Vendor__c, Rating__c, Comment__c, Phone__c FROM Review__r) FROM Category__c WHERE Id IN: setCategoryIds]);
RyanSchierholzRyanSchierholz
Yes! That is compiling, and I'm able to bring results into the lwc! Thank you! You've done more than enough; I hope to be able to tackle the next steps on my own, but you're more than welcome to contribute your expertise: 1. Only show the Super Categories and Categories in which there are reviews (of vendors) 2. If there are more than one review for a Vendor in a category, only show the Vendor/Review once, but show the number of 'duplicates'. 3. Ability to filter through search Ryan Schierholz
Nayana KNayana K
By looking at #1,#2,#3, seems reverse way of traversing through soql queries like Review.Category.SupeeCategory or Category.SuperCatery and Category.Reviews kind of queries should help. Curently, I am travelling.Available only one mobile. Can't type code easily.
Monday my morning, I will try to check. Sorry about that.
Nayana KNayana K
1. Only show the Super Categories and Categories in which there are reviews (of vendors) 
    -- Assuming we need only super categories having atleast one Categorys.Reviews
2. If there are more than one review for a Vendor in a category, only show the Vendor/Review once, but show the number of 'duplicates'. 
    - Group by Review.Vendor__c
3. Ability to filter through search Ryan Schierholz
    - I assume, if searchTerm is passed to the fetchData(), then user want to search. If empty string is passed, we display all super categories without filter. 
 
public class CategoriesController {
	@AuraEnabled
	public static List<SuperCategory> fetchData(String searchTerm) {
	
		SuperCategory tempSC;
		Category tempCategory;
		Review tempReview;
		
		Map<Id, SuperCategory> mapSuperCategory = new Map<Id, SuperCategory>();
		Map<String, Review> mapVendorToReview;
		String query = '';
		query += 'SELECT Id, Name, Super_Category__c, Super_Category__r.Name, (SELECT Id, Vendor__c, Rating__c, Comment__c, Phone__c FROM Reviews__r';
		if(String.isNotBlank()) {
			query += ' WHERE Vendor__c =: searchTerm';
		}
		query += ') FROM PVL_Category__c WHERE Super_Category__c != NULL ';
		
		
		for(PVL_Category__c objCategory : Database.query(query)) {
			// if atleast one review
			if(!objCategory.Reviews__r.isEmpty()) {
				tempSC = new SuperCategory();
				tempCategory = new Category();
				
				if(mapSuperCategory.containsKey(objCategory.Super_Category__c)) {
					tempSC = mapSuperCategory.get(objCategory.Super_Category__c);
					
				}
				else {
					tempSC = new SuperCategory(objCategory.Super_Category__c, objCategory.Super_Category__r.Name);
					mapSuperCategory.put(objCategory.Super_Category__c, tempSC);
				}
				
				tempCategory = new Category(objCategory.Id, objCategory.Name);
				mapVendorToReview = new Map<String, Review>();
				
				for(Review__c objReview : objCategory.Reviews__r)) {
					if(mapVendorToReview.containsKey(objReview.Vendor__c)) {
						mapVendorToReview.get(objReview.Vendor__c).dupeCount++;
					}
					else {
						mapVendorToReview.put(objReview.Vendor__c, new Review(objReview.Id, objReview.Vendor__c, objReview.Rating__c != null ? Integer.valueOf(objReview.Rating__c) : null, 
																				objReview.Comment__c, objReview.Phone__c, 1));
					}
				}
				tempCategory.Reviews.addAll(mapVendorToReview.values());
				tempSC.Categories.add(tempCategory);
			}
		}
		
		return mapSuperCategory.values();
	}
	
	public class SuperCategory {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled 
		public List<Category> Categories {get;set;}
		
		public SuperCategory(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Categories = new List<Category>();
		}
	}
	
	public class Category {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Name {get;set;}
		@AuraEnabled
		public List<Review> Reviews {get;set;}
		
		public Category(Id Id, String Name) {
			this.Id = Id;
			this.Name = Name;
			Reviews = new List<Review>();
		}
	}
	
	public class Review {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled
		public String Vendor {get;set;}
		@AuraEnabled
		public Integer Rating {get;set;}
		@AuraEnabled
		public String Comment {get;set;}
		@AuraEnabled
		public String Phone {get;set;}
		@AuraEnabled 
		public Integer dupeCount {get;set;}
		
		public Review(Id Id, String Vendor, Integer Rating, String Comment, String Phone, Integer dupeCount) {
			this.Id = Id;
			this.Vendor = Vendor;
			this.Rating = Rating;
			this.Comment = Comment;
			this.Phone = Phone;
			this.dupeCount = dupeCount;
		}
	}
}

Please mark this as solved if it helped you.
This was selected as the best answer
RyanSchierholzRyanSchierholz
Thanks again for the help! I had worked out a somewhat different solution for #1 over the weekend, but yours works well too! #3 I had adapted from another project and included variables in the query so it looks like:
@AuraEnabled(cacheable=true)
	public static List<SuperCategory> fetchData(String searchKey, Boolean mineOnly) {
	
		SuperCategory tempSC;
		Category tempCategory;
		Review tempReview;
		
		Map<Id, SuperCategory> mapSuperCategory = new Map<Id, SuperCategory>();
		Map<String, Review> mapVendorToReview;
        String key = '%' + searchKey + '%';
		List<Boolean> myVendorFilter = new List<Boolean>();
		myVendorFilter.clear();
		myVendorFilter.add(true);
		if(mineOnly==false){
			myVendorFilter.add(false);
		}

		String query = 'SELECT Id, Name, Super_Category__c, Super_Category__r.Name, Standard_Name__c,(SELECT Id, Vendor__c, Rating__c, Comment__c, Phone__c, MyVendor__c, Contact__c, Contact__r.Greeting_Informal_Short__c, Contact__r.Greeting_Informal_Long__c, Company__c, Company__r.Name,Company_Rating__c, Contact_Rating__c FROM Reviews__r WHERE MyVendor__c IN :myVendorFilter AND (Vendor__c LIKE :key OR Category__r.Name LIKE :key)) FROM PVL_Category__c WHERE Super_Category__c != NULL ';

Thank you for #2. I can show the dupeCount on the Lightning web component. However, I thought I could use that number to return a Boolean of whether there are duplicates or not. I only want to show the number if its greater than 1. 

So, at the bottom of the Review class I use the code:
this.dupeCount = dupeCount;
            if(dupeCount > 1){
                this.hasDupes = true;
            } else{
                this.hasDupes = false;
            }

and then reference the hasDupes in the LWC. However, it's always showing as false, even when the number shows as "2"

User-added image

User-added image
Wondering what I could be missing or how to hide the "1" but show the "2"
Nayana KNayana K
One option is to set the boolean while filling Reviews data. Eg:
for(Review__c objReview : objCategory.Reviews__r)) {
					if(mapVendorToReview.containsKey(objReview.Vendor__c)) {
						mapVendorToReview.get(objReview.Vendor__c).dupeCount++;
mapVendorToReview.get(objReview.Vendor__c).hasDupes = true;
					}
					else {
						mapVendorToReview.put(objReview.Vendor__c, new Review(objReview.Id, objReview.Vendor__c, objReview.Rating__c != null ? Integer.valueOf(objReview.Rating__c) : null, 
																				objReview.Comment__c, objReview.Phone__c, 1));
					}
				}

Then initialize the hasDupes to false in the wrapper class constructor
Nayana KNayana K
If still in confusion, please share the entire method code(if you have followed different approach). Let's see if I can figure it out.