SOQL – Dynamic Selection of Fields for WHERE Clause in SOQL Queries

I need to retrieve certain Custom Metadata records from my Custom Metadata Type Milestone_Template__mdt, depending on which of my company's regions I am creating Projects & Milestones for in my code.

The Milestone_Template__mdt records have a tickbox on them, which are named like EU_Milestone__c, to identify the region that they belong too. I've deliberately not used a picklist for reasons that I'd be happy to explain the comments, if anyone's wondering.

To avoid wasting characters, I'd like to avoid writing a separate query for each of the different regions that I could potentially be creating those records for & deciding which query to execute via if / else statements.

Instead I'm hoping that there's a way to write my query to specify which region's records to select, depending on a region that's stored in a variable :region.

I had the not so bright idea of referencing the variable like this

projectTemplateWithMilestoneTemplates = [SELECT Project_Name__c,
                                        (SELECT Milestone_Name__c
                                           FROM Milestone_Templates__r
                                          WHERE ((:region = 'EU' AND EU_Milestone__c = TRUE) OR (:region = 'NA' AND NA_Milestone__c = TRUE)) AND Test_Record__c = FALSE)
                                           FROM Project_Template__mdt];

the idea being that the criteria would only evaluate to TRUE if the region was the one specified & the region's tickbox was ticked but it looks like that's not allowed.

Is there another way that I can check whether a certain tickbox is ticked on these records, depending on the value stored in my variable? Or failing that, is there another way to approach this process?

The data model looks like:

Project_Template__mdt
       |
Milestone_Template__mdt

Best Answer

Ok, I think your best bet is to use a Dynamic SOQL query (assuming that Salesforce allows Dynamic MDT queries - if not, I'll remove this answer).

Seperate out your region condition and then just fold it into the main query, then execute like this:

String regionClause = ( region == 'EU' ? ' EU_Milestone__c = TRUE ' : 
                       (region == 'US' ? ' US_Milestone__c = TRUE ' :
                                         ' ASIA_Milestone__c = TRUE '));

String query =        'SELECT Project_Name__c,            ';
       query +=       '   (SELECT Milestone_Name__c       ';
       query +=       '    FROM Milestone_Templates__r    ';
       query +=       '    WHERE ' + regionClause + '     ';
       query +=       '       AND Test_Record__c = FALSE) ';
       query +=       'FROM Project_Template__mdt         ';

projectTemplateWithMilestoneTemplates = Database.query(query);

Good luck!

Related Topic