Dynamic Selection of Fields for WHERE Clause in SOQL Queries

Question

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

Answer 1

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!

Answer 2

I think would create a utility method that just builds a map of Region to List<Milestone_Template__mdt> by looping through all the records to create the map. Then the code can just pass in a region as a param and get back all the records as a list...