Wire Date Literals to Apex Controller

Question

Hi I'm needing help working with sending date literals from a wire method to my controller. LWC JS:

import {LightningElement, wire, track,api} from 'lwc';
import getOutgoings from '@salesforce/apex/PropertyCostReportController.getAllOutgoings';



export default class propertyOutgoingsChartLWC extends LightningElement {
    error;
    bodyCorps;
    @api recordId;
    chartData;
    data;
    chartjsInitialized = false;
    dateValue;
    get options() {
        return [
            { label: new Date().getFullYear(), value: "THIS_YEAR" },
            { label: new Date().getFullYear()-1, value: "LAST_YEAR" },
            { label: new Date().getFullYear()-2, value: "LAST_N_YEARS:2" },
            
        ];
    }

  

    @wire(getOutgoings, {recordId:'$recordId', dateValue:'$dateValue'})
    getOutgoings({error, data}) {

        if (error) {
            this.error = error;
            console.log('error => ' + JSON.stringify(error));
            
        } else if (data) {
            let chartData =[];
            
            data.forEach(og => {
                let dataline = {};
                dataline.chartData = og.allTotal;
                dataline.bodyCorps = og.bcTotal;
                dataline.councilData = og.crTotal;
                dataline.waterData = og.wrTotal;
                dataline.maintenanceData = og.mcTotal;
                dataline.landTaxData = og.ltTotal;
                dataline.esmData = og.esmTotal;
                chartData.push(dataline);
                console.log(chartData);
            });
            this.data = chartData;
            this.error = undefined;
            console.log('BodyCorps => ', chartData);
               console.log('data => ', data);
    }
    
}
handleChange(event) {
    this.dateValue = event.detail.value;
}
}

APEX Class:

public with sharing class PropertyCostReportController {
    
    String recordId {get;set;}
    Date dateValue {get;set;}
    
    @AuraEnabled(cacheable=true)
    public static List<OutgoingsWrapper> getAllOutgoings(String recordId, Date dateValue){
        List<OutgoingsWrapper> outgoingsWrapperList = new List<OutgoingsWrapper>();
        List<AggregateResult> bcList = [SELECT sum(Amount__c) total 
        FROM Body_Corporate__c WHERE Property__c =:recordId AND Period_End__c =:dateValue];
        

        List<AggregateResult> ltList = [SELECT sum(Amount__c) total 
        FROM Land_Tax__c WHERE Property__c =:recordId  AND Period_End__c =:dateValue];

        List<AggregateResult> crList = [SELECT sum(Amount__c) total 
        FROM Council_Rates__c WHERE Property__c =:recordId  AND Period_End__c =:dateValue];

        List<AggregateResult> wrList = [SELECT sum(Amount__c) total 
        FROM Water_rates__c WHERE Property__c =:recordId  AND Period_End__c =:dateValue];

        List<AggregateResult> mcList = [SELECT sum(Amount__c) total 
        FROM Maintenance_Costs__c WHERE Property__c =:recordId  AND Period_End__c =:dateValue];

        List<AggregateResult> esmList = [SELECT sum(Amount__c) total 
        FROM ESM__c WHERE Property__c =:recordId  AND Period_End__c =:dateValue];

        for(AggregateResult bc : bcList)
        for(AggregateResult cr : crList)
        for(AggregateResult wr : wrList)
        for(AggregateResult lt : ltList)
        for(AggregateResult mc : mcList)
        for(AggregateResult esm : esmList){
                OutgoingsWrapper ogWrapper = new OutgoingsWrapper();
                ogWrapper.bcTotal = (Decimal)bc.get('total')==null?0:(decimal)bc.get('total');
                ogWrapper.crTotal = (Decimal)cr.get('total')==null?0:(decimal)cr.get('total');
                ogWrapper.wrTotal = (Decimal)wr.get('total')==null?0:(decimal)wr.get('total');
                ogWrapper.mcTotal = (Decimal)mc.get('total')==null?0:(decimal)mc.get('total');
                ogWrapper.esmTotal = (Decimal)esm.get('total')==null?0:(decimal)esm.get('total');
                ogWrapper.ltTotal = (Decimal)lt.get('total')==null?0:(decimal)lt.get('total');
                ogWrapper.allTotal = ogWrapper.ltTotal+ogWrapper.bcTotal+ogWrapper.crTotal+ogWrapper.wrTotal+ogWrapper.mcTotal+ogWrapper.esmTotal;
                outgoingsWrapperList.add(ogWrapper);
            }
        
        return outgoingsWrapperList;
    }
      
    // wrapper class with @AuraEnabled and {get;set;} properties 
    public class OutgoingsWrapper{
        @AuraEnabled
        public Decimal bcTotal{get;set;}
        @AuraEnabled
        public Decimal crTotal{get;set;}
        @AuraEnabled
        public Decimal wrTotal{get;set;}
        @AuraEnabled
        public Decimal mcTotal{get;set;}
        @AuraEnabled
        public Decimal ltTotal{get;set;}
        @AuraEnabled
        public Decimal esmTotal{get;set;}
        @AuraEnabled
        public Decimal allTotal{get;set;}
       
            
    }


}

I've tried a few different things:

• input date which works fine
• just a string like '2022-12-31' which again brings the data.
• If I hard code 'THIS_YEAR' or 'LAST_YEAR' in the SOQL it works fine.
• changing the dateValue from a 'Date' to a 'String' which didn't work. (I was under the impression after reading on another post that a date literal was type 'Date' and not a string)

in console I'm getting '"message":"Value provided is invalid for action parameter 'dateValue' of type 'Date'"}'

What I'm really trying to achieve is a picklist of years like 2022,2021,2020 which this brings all the totals with the 'Period End date' in that year.

In this screen grab the selected value is 'THIS_YEAR'

In this screen grab the selected value is '2022-12-31' which brings through the data.

Any insight would be fantastic, Thank you.

Answer 1

That's an odd limitation, but it makes sense; the compiler is compiling the query before runtime, so it can only verify bind parameters that are of a compatible type. To fix this, you'll need to use the Dynamic SOQL version of this code instead:

 AggregateResult[] bc = Database.query('SELECT sum(Amount__c) total FROM Land_Tax__c WHERE Property__c =:recordId  AND Period_End__c = '+dateValue];

Of course, this is insecure, so make sure your date values are sanitized with String.escapeSinqleQuote.