0

I am trying to accomplish something like the following:

[SELECT Id, CustomMember__c, CreatedDate 
FROM CustomObject 
WHERE CreatedDate = LAST_N_MONTHS:CustomMember__r.CustomLevel__r.CustomPeriod__c
AND Id IN :customObjectIds];

This right now is giving me the error "expecting a number, found 'xxxx'".

Basically I need to get all the CustomObjects with certain ids that I already have and with CreatedDate between the last N months. The problem is that N change between records is not a fixed value.

Is there a way to achieve something like this directly with SOQL so the query can filter last N moths dynamically or do I have to get all the records and then separately loop through them to filter?

Thanks.

2 Answers 2

2

The various date literals are all documented to accept a number N. They do not permit you to use a dynamic expression or field reference.

You'll have to query the records and filter them in Apex, or change your data model to include a formula field that encapsulates this logic - say, a Checkbox field whose value is True only when the created date of the record is within the given months. Then, you could query on the formula field. Note of course that this may have performance implications, and if I understand your model correctly you won't be able to have that formula field indexed because it will have to have a reference to the current date.

1
  • 1
    To improve the SOQL query performance with such an in-memory evaluated formula field, make sure to put the Id IN :customObjectIds clause first. In our experience (we last did the analysis against Summer '20 it should be said) SOQL doesn't automatically optimize queries despite what you might read.
    – Phil W
    Jan 18, 2021 at 22:34
1

Edit: make sure that CustomPeriod__c isn't a picklist or something.

If not, I don't think you can mix the SOQL and the field value like that. But you don't need to query all records. Just query for your criteria, set it as a variable, then query the list. Here's an example that assumes you want the max CustomPeriod__c:

Date referencePeriod = [SELECT CustomPeriod__c 
                  FROM CustomObject 
                  //other criteria here...
                  Order by CustomPeriod__c DESC LIMIT 1].CustomPeriod__c;

List<CustomObject> oList = [SELECT Id, CustomMember__c, CreatedDate 
                            FROM CustomObject 
                            WHERE CreatedDate >=:referencePeriod  
                            AND Id IN :customObjectIds];

You could adapt the query to meet any criteria you'd like.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .