0

I want to make the following dynamic soql to static soql. I'm a bit confused as I haven't done anything like this before. How can it be done? Any help on this would be appreciated. Thanks in advance!

public List<Metric__c> getManualMetrics(Set <String> frequencyTypes){

        Set<Id> relatedMetricIds = new Set<Id>();
        boolean limitByObjectId = false;
        //Check for attached Metric objects
        if(objectId != null){
            relatedMetricIds = SObjectHelper.getRecordIdsForRelatedObjectType(objectId, Metric__c.sObjectType);
            limitByObjectId = true;
        }

        List<Metric__c> metricsList = new List<Metric__c>();

        if(frequencyTypes == null || frequencyTypes.size() == 0){
            // TODO
            String queryString = 'SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = \'Manual\'';

            if (limitByObjectId){
                queryString = queryString + ' AND Id IN :relatedMetricIds';
            }

            queryString = queryString + ' order by Name asc';

            metricsList = database.query(queryString);

        } else {

            String queryString = 'SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = \'Manual\' AND Monitoring_Frequency__c IN :frequencyTypes';

            if (limitByObjectId){
                queryString = queryString + ' AND Id IN :relatedMetricIds';
            }

            queryString = queryString + ' order by Name asc';

            metricsList = database.query(queryString);
        }
        return metricsList;

    }
  • 4
    Why are you trying to make the change? A key difference between dynamic & static SOQL is that only dynamic SOQL can be built 'on the fly' -- so some of what you're doing here, such as conditionally including filter criteria, wouldn't be possible in static soql. – Benj Jan 12 '15 at 16:47
  • 1
    You would need a number of hard-coded queries based on the parameters passed. Not sure why you'd want that maintenance nightmare, but... – sfdcfox Jan 12 '15 at 16:50
1

Pretty much replace all of:

String queryString = 'SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = \'Manual\' AND Monitoring_Frequency__c IN :frequencyTypes';

if (limitByObjectId){
  queryString = queryString + ' AND Id IN :relatedMetricIds';
}

queryString = queryString + ' order by Name asc';
metricsList = database.query(queryString);

with:

metricsList = [SELECT Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source = 'Manual' AND Monitoring_Frequency IN :frequencyTypes];

It's important to note that I did not add your conditional check in. Like the other commenter stated, you need to use dynamic SOQL in order to do so. Using static SOQL makes you lose this ability.

0

I managed to do the conditions in the following way and it started working fine!

        List<Metric__c> metricsList = new List<Metric__c>();

        if(frequencyTypes == null || frequencyTypes.size() == 0){

            if (limitByObjectId){
                metricsList = [SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = 'Manual' AND Id IN :relatedMetricIds order by Name asc];
            }else{
                metricsList = [SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = 'Manual' order by Name asc];
            }

            /*String queryString = 'SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = \'Manual\'';

            if (limitByObjectId){
                queryString = queryString + ' AND Id IN :relatedMetricIds';
            }

            queryString = queryString + ' order by Name asc';

            metricsList = database.query(queryString);*/

        } else {

            if (limitByObjectId){
                metricsList = [SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = 'Manual' AND Monitoring_Frequency__c IN :frequencyTypes AND Id IN :relatedMetricIds order by Name asc];
            }
            else{
                metricsList = [SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = 'Manual' AND Monitoring_Frequency__c IN :frequencyTypes order by Name asc];
            }

            /*String queryString = 'SELECT Id, Name, Metric_Type__c, Use_Yellow_Threshold__c, Yellow_Threshold_Text__c, Yellow_Currency_Threshold__c, Yellow_Threshold__c, Amber_Threshold__c, Description__c, Amber_Threshold_Text__c, Green_Threshold_Text__c, Monitoring_Frequency__c, Target__c, Red_Threshold__c, Red_Threshold_Text__c, Threshold_Type__c, Type__c, Amber_Currency_Threshold__c, Red_Currency_Threshold__c, Target_Currency__c, Active__c FROM Metric__c WHERE Data_Source__c = \'Manual\' AND Monitoring_Frequency__c IN :frequencyTypes';

            if (limitByObjectId){
                queryString = queryString + ' AND Id IN :relatedMetricIds';
            }

            queryString = queryString + ' order by Name asc';

            metricsList = database.query(queryString);*/
        }
        return metricsList;

    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.