Dynamic SOQL Query -> Select Sum(Custom Metadata Field) Question

Question

This is my first intro with custom metadata and also dynamic soql.

I am trying to set it up so that users can set up certain fields that they would like to use for the apex class calculation via Custom Metadata.

My question is around the formatting of the SOQL query which uses aggregate functions specifically the following piece: 'SELECT OpportunityId, Product2.Family, Sum(salesPriceProxy) salesPrice, Sum(listPriceProxy) listPrice ' -> I'm getting a bit lost with the + and \ here.

Please see the larger snippet below:

List<Product_Family_Discount_Setting__mdt> defaultSetting =[
            SELECT Sales_Price_Field__c, List_Price_Field__c
            FROM Product_Family_Discount_Setting__mdt
            LIMIT 1
        ];

        System.debug('These are the Default Settings:::::::::'+defaultSetting); 
        System.debug('This is the first element of the list::::::'+defaultSetting[0]);
        
        String salesPriceProxy = (String)defaultSetting[0].get('Sales_Price_Field__c');
        String listPriceProxy = (String)defaultSetting[0].get('List_Price_Field__c');

        System.debug('salesPrice field ==========='+ salesPriceProxy);
        System.debug('listPrice field ============'+ listPriceProxy);

        String query = 
        'SELECT OpportunityId, Product2.Family, Sum(salesPriceProxy) salesPrice, Sum(listPriceProxy) listPrice '+
        'FROM OpportunityLineItem '+
        'WHERE OpportunityId IN: opportunityIdFromTriggerMap '+
        'GROUP BY OpportunityId, Product2.Family';

        List<AggregateResult> oppProdGroupedByFam = Database.query(query);

All feedback is very much appreciated!

Answer 1

You can make your life easier (in my opinion, of course; perceptions of readability may differ) by using string formatting to inject the dynamic elements of your query, rather than adding more concatenation.

Note that you cannot use an Apex variable name (or a bind, for that matter) in the SELECT clause of your query; you must construct it yourself.

String query = String.format(
    'SELECT OpportunityId, Product2.Family, SUM({0}) salesPrice, SUM({1}) listPrice ' +
    'FROM OpportunityLineItem ' +
    'WHERE OpportunityId IN: opportunityIdFromTriggerMap ' +
    'GROUP BY OpportunityId, Product2.Family',
    new List<String>{salesPriceProxy, listPriceProxy}
);