0

Hi i have a custom object object name Businesscase and it has a custom lookupfield to another object is Division.I have an junction object that has the two fields one is master detail of the businesscase and another field is lookup of businesscase now i want to query the all the businesscase records and divisions which are related to the lookup of business case in the junction object. Iam using the two queries first iam querying the all the businesscases records in one query from that id's iam querying the junction object divisions now can i query the both in the single query

List<BC__c> bcList = [SELECT Id, Business_Case_Name__c From BC__c WITH SECURITY_ENFORCED limit 10000];

1st query

List<BC_Related__c> relatedBCList = [SELECT id,Related_Business_Case__r.id,Related_Business_Case__r.Organization__c,Related_Business_Case__r.Organization__r.Name,Related_Business_Case__r.Organization__r.id,Business_Case__r.id From BC_Related__c where Related_Business_Case__r.id =:bcIds];

2nd query Here in the first query BC__c is businesscase and BC_Related__c is the junction object

1 Answer 1

0

First, when you're retrieving Id through lookup in SOQL, you are not obligated to do it like Related_Business_Case__r.Id, Related_Business_Case__c is enough. So in the second query Related_Business_Case__r.Organization__c and Related_Business_Case__r.Organization__r.Id are returning the same result, so you may omit the second one.

I have also added field Related_Business_Case__r.Business_Case_Name__c in the second query from the first one, assuming we will not have it retrieved within join.

Moreover, you will need to move WITH SECURITY_ENFORCED to the outer query.

Second, to join these queries, you'll need to use IN keyword and substitute your bind-variable with actual first query:

List<BC_Related__c> relatedBCList = [
    SELECT Id, Related_Business_Case__c, Related_Business_Case__r.Organization__c, Related_Business_Case__r.Organization__r.Name, Business_Case__c, Related_Business_Case__r.Business_Case_Name__c
    FROM BC_Related__c 
    WHERE Related_Business_Case__c IN (
        SELECT Id 
        FROM BC__c 
        LIMIT 10000
    )
    WITH SECURITY_ENFORCED 
];

According to the comment regarding putting Related_Business_Case__c in the Map with BC_Related__c as a key (all you'll need to do is just run through query results and store them in map):

Map<Id, List<BC_Related__c>> businessCaseToRelatedBC = new Map<Id, List<BC_Related__c>>();
for (BC_Related__c bc : [
    SELECT Id, Related_Business_Case__c, Related_Business_Case__r.Organization__c, Related_Business_Case__r.Organization__r.Name, Business_Case__c, Related_Business_Case__r.Business_Case_Name__c
    FROM BC_Related__c 
    WHERE Related_Business_Case__c IN (
        SELECT Id 
        FROM BC__c 
        LIMIT 10000
    )
    WITH SECURITY_ENFORCED 
]) {
    if (!businessCaseToRelatedBC.containsKey(bc.Related_Business_Case__c)) {
        businessCaseToRelatedBC.put(bc.Related_Business_Case__c, new List<BC_Related__c>());
    }
    businessCaseToRelatedBC.get(bc.Related_Business_Case__c).add(bc);
}
4
  • Hi Mariia it is really help ful but i have a doubt i need a hashmap in that hash map i need to store the all the related businesscase divisions this map needs to be rotate in the Businesscase list how can i do that by using the single query Nov 19, 2021 at 9:09
  • @prasannakumar I have updated the answer: all you'll need to do is just run through query results and store them in map. Nov 19, 2021 at 9:18
  • thank you can you help me on how to rotate the nested list in the lwc html template i have nested lists that needs to display the table cell i tried in this way '<td class="slds-cell-wrap"> <template for:each= {bc.urlItems} for:item ='bc' > <br key ={bc}><a key= {bc.accountUrl} href = {bc.accountUrl}>{bc.accountName}</a> </template> </td>' i tried this one i got the data in the console but it is not displaying on the td Nov 19, 2021 at 12:14
  • @prasannakumar Sorry, it is another question, so better post it as a separate one. I'm not capable of helping you with lwc - I'm still new to it. So if I have answered the original question, please, accept it. Nov 19, 2021 at 16:20

You must log in to answer this question.

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